Can I concatenate multiple MySQL rows into one field?

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 http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws">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?

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

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';

Alternate syntax to concatenate multiple, individual rows

WARNING: This post will make you hungry.

Given:

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...

Find:

A way to summarize your user's order in one line, with pure mysql.

Solution:

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 |
+------------------------------------------------+

Bonus Solution:

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 |
+------------------------------------------------+-------+

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.

There's a GROUP Aggregate function, http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">GROUP_CONCAT</a>;.

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

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

I had a more complicated query, and found that I had to use GROUP_CONCAT in an outer query to get it to work:

Original Query:

SELECT DISTINCT userID 
FROM event GROUP BY userID 
HAVING count(distinct(cohort))=2);

Imploded:

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.

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;

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

It is late but will helpfull for those who are searching "concatenate multiple MySQL rows into one field using pivot table" :)

Query:

SELECT pm.id, pm.name, GROUP_CONCAT(c.name) as channel_names
FROM payment_methods pm
LEFT JOIN payment_methods_channels_pivot pmcp ON pmcp.payment_method_id = pm.id
LEFT JOIN channels c ON c.id = pmcp.channel_id
GROUP BY pm.id

Tables

payment_methods 
  id  | name
  1   | PayPal

channels
  id  | name
  1   | Google
  2   | Faceook

payment_methods_channels_pivot
   payment_method_id | channel_id
   1                 |  1
   1                 |  2

Output:

Here, my intension was to apply string concatenation without using group_concat() function:

Set @concatHobbies = '';
SELECT TRIM(LEADING ', ' FROM T.hobbies ) FROM 
(
   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies
)T
Order by Id DESC
LIMIT 1

Here

   select 
   Id, @concatHobbies := concat_ws(', ',@concatHobbies,hobbies) as hobbies
   from peoples_hobbies

will return

  Id	hobbies
  1	    , shopping
  2	    , shopping, fishing
  3	    , shopping, fishing, coding

Now our expected result is at third position. So I am taking the Last row by using

  Order by Id DESC 
  LIMIT 1
  

Then I am also removing the First ', ' from my string

 TRIM(LEADING ', ' FROM T.hobbies )

In sql server use string_agg to pivot a row field values into a column:

select string_agg(field1, ', ') a FROM mytable 

or

select string_agg(field1, ', ') within group (order by field1 dsc) a FROM mytable group by field2

Another interesting example in this case -

Following is the structure of the sample table people_hobbies -

DESCRIBE people_hobbies;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| ppl_id  | int unsigned | YES  | MUL | NULL    |                |
| name    | varchar(200) | YES  |     | NULL    |                |
| hby_id  | int unsigned | YES  | MUL | NULL    |                |
| hobbies | varchar(50)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

The table is populated as follows -

SELECT * FROM people_hobbies;
+----+--------+-----------------+--------+-----------+
| id | ppl_id | name            | hby_id | hobbies   |
+----+--------+-----------------+--------+-----------+
|  1 |      1 | Shriya Jain     |      1 | reading   |
|  2 |      4 | Shirley Setia   |      4 | coding    |
|  3 |      2 | Varsha Tripathi |      7 | gardening |
|  4 |      3 | Diya Ghosh      |      2 | fishing   |
|  5 |      4 | Shirley Setia   |      3 | gaming    |
|  6 |      1 | Shriya Jain     |      6 | cycling   |
|  7 |      2 | Varsha Tripathi |      1 | reading   |
|  8 |      3 | Diya Ghosh      |      5 | shopping  |
|  9 |      3 | Diya Ghosh      |      4 | coding    |
| 10 |      4 | Shirley Setia   |      1 | reading   |
| 11 |      1 | Shriya Jain     |      4 | coding    |
| 12 |      1 | Shriya Jain     |      3 | gaming    |
| 13 |      4 | Shirley Setia   |      2 | fishing   |
| 14 |      4 | Shirley Setia   |      7 | gardening |
| 15 |      2 | Varsha Tripathi |      3 | gaming    |
| 16 |      2 | Varsha Tripathi |      2 | fishing   |
| 17 |      1 | Shriya Jain     |      5 | shopping  |
| 18 |      1 | Shriya Jain     |      7 | gardening |
| 19 |      3 | Diya Ghosh      |      1 | reading   |
| 20 |      4 | Shirley Setia   |      5 | shopping  |
+----+--------+-----------------+--------+-----------+

Now, a table hobby_list is generated having the list of all people and a list of each person's hobbies with each hobby in a new line -

CREATE TABLE hobby_list AS
    -> SELECT ppl_id, name,
    -> GROUP_CONCAT(hobbies ORDER BY hby_id SEPARATOR "\n")
    -> AS hobbies
    -> FROM people_hobbies
    -> GROUP BY ppl_id
    -> ORDER BY ppl_id;

SELECT * FROM hobby_list;