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

16 Answers

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

I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.

Given:

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

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:

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.

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; 

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 ) 

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; 

Use GROUP_CONCAT:

SELECT GROUP_CONCAT(hobbies) FROM peoples_hobbies WHERE person_id = 5; 

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