Using MySQL, I can do something like:
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
My Output:
shopping
fishing
coding
but instead I just want 1 row, 1 col:
Expected Output:
shopping, fishing, coding
The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.
I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets.
So does anyone here know how to do this?
This question is related to
mysql
sql
concat
group-concat
Use MySQL(5.6.13) session variable and assignment operator like the following
SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;
then you can get
test1,test11
For somebody looking here how to use GROUP_CONCAT with subquery - posting this example
SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid
So GROUP_CONCAT must be used inside the subquery, not wrapping it.
Try this:
DECLARE @Hobbies NVARCHAR(200) = ' '
SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;
TL;DR;
set @sql='';
set @result='';
set @separator=' union \r\n';
SELECT
@sql:=concat('select ''',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ,''' as col_name,',
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH ,' as def_len ,' ,
'MAX(CHAR_LENGTH(',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , '))as max_char_len',
' FROM ',
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
) as sql_piece, if(@result:=if(@result='',@sql,concat(@result,@separator,@sql)),'','') as dummy
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE like '%char%'
and INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='xxx'
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='yyy';
select @result;
Have a look at GROUP_CONCAT if your MySQL version (4.1) supports it. See the documentation for more details.
It would look something like:
SELECT GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
WHERE person_id = 5
GROUP BY 'all';
Try this:
DECLARE @Hobbies NVARCHAR(200) = ' '
SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;
TL;DR;
set @sql='';
set @result='';
set @separator=' union \r\n';
SELECT
@sql:=concat('select ''',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ,''' as col_name,',
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH ,' as def_len ,' ,
'MAX(CHAR_LENGTH(',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , '))as max_char_len',
' FROM ',
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
) as sql_piece, if(@result:=if(@result='',@sql,concat(@result,@separator,@sql)),'','') as dummy
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE like '%char%'
and INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='xxx'
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='yyy';
select @result;
we have two way to concatenate columns in MySql
select concat(hobbies) as `Hobbies` from people_hobbies where 1
Or
select group_concat(hobbies) as `Hobbies` from people_hobbies where 1
There's a GROUP Aggregate function, GROUP_CONCAT.
Have a look at GROUP_CONCAT if your MySQL version (4.1) supports it. See the documentation for more details.
It would look something like:
SELECT GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
WHERE person_id = 5
GROUP BY 'all';
There's a GROUP Aggregate function, GROUP_CONCAT.
Use MySQL(5.6.13) session variable and assignment operator like the following
SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;
then you can get
test1,test11
There's a GROUP Aggregate function, GROUP_CONCAT.
I had a more complicated query, and found that I had to use GROUP_CONCAT in an outer query to get it to work:
SELECT DISTINCT userID
FROM event GROUP BY userID
HAVING count(distinct(cohort))=2);
SELECT GROUP_CONCAT(sub.userID SEPARATOR ', ')
FROM (SELECT DISTINCT userID FROM event
GROUP BY userID HAVING count(distinct(cohort))=2) as sub;
Hope this might help someone.
WARNING: This post will make you hungry.
I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.
Let's say you have a table of product ids and their names and prices:
+------------+--------------------+-------+
| product_id | name | price |
+------------+--------------------+-------+
| 13 | Double Double | 5 |
| 14 | Neapolitan Shake | 2 |
| 15 | Animal Style Fries | 3 |
| 16 | Root Beer | 2 |
| 17 | Lame T-Shirt | 15 |
+------------+--------------------+-------+
Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.
Your hungry-hippo user selects 13, 15, 16. No dessert for her today...
A way to summarize your user's order in one line, with pure mysql.
Use GROUP_CONCAT with the the IN clause:
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);
Which outputs:
+------------------------------------------------+
| order_summary |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+
If you want the total price too, toss in SUM():
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer | 10 |
+------------------------------------------------+-------+
we have two way to concatenate columns in MySql
select concat(hobbies) as `Hobbies` from people_hobbies where 1
Or
select group_concat(hobbies) as `Hobbies` from people_hobbies where 1
In my case I had a row of Ids, and it was neccessary to cast it to char, otherwise, the result was encoded into binary format :
SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table
I had a more complicated query, and found that I had to use GROUP_CONCAT in an outer query to get it to work:
SELECT DISTINCT userID
FROM event GROUP BY userID
HAVING count(distinct(cohort))=2);
SELECT GROUP_CONCAT(sub.userID SEPARATOR ', ')
FROM (SELECT DISTINCT userID FROM event
GROUP BY userID HAVING count(distinct(cohort))=2) as sub;
Hope this might help someone.
You can change the max length of the GROUP_CONCAT value by setting the group_concat_max_len parameter.
See details in the MySQL documantation.
You can change the max length of the GROUP_CONCAT value by setting the group_concat_max_len parameter.
See details in the MySQL documantation.
For somebody looking here how to use GROUP_CONCAT with subquery - posting this example
SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid
So GROUP_CONCAT must be used inside the subquery, not wrapping it.
There's a GROUP Aggregate function, GROUP_CONCAT.
WARNING: This post will make you hungry.
I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.
Let's say you have a table of product ids and their names and prices:
+------------+--------------------+-------+
| product_id | name | price |
+------------+--------------------+-------+
| 13 | Double Double | 5 |
| 14 | Neapolitan Shake | 2 |
| 15 | Animal Style Fries | 3 |
| 16 | Root Beer | 2 |
| 17 | Lame T-Shirt | 15 |
+------------+--------------------+-------+
Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.
Your hungry-hippo user selects 13, 15, 16. No dessert for her today...
A way to summarize your user's order in one line, with pure mysql.
Use GROUP_CONCAT with the the IN clause:
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);
Which outputs:
+------------------------------------------------+
| order_summary |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+
If you want the total price too, toss in SUM():
mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer | 10 |
+------------------------------------------------+-------+
Have a look at GROUP_CONCAT if your MySQL version (4.1) supports it. See the documentation for more details.
It would look something like:
SELECT GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
WHERE person_id = 5
GROUP BY 'all';
In my case I had a row of Ids, and it was neccessary to cast it to char, otherwise, the result was encoded into binary format :
SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table
Source: Stackoverflow.com