Can I concatenate multiple MySQL rows into one field?

Question

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?

I just wrote up a little demo on how to use group_concat which might be usefull to you: giombetti.com/2013/06/06/mysql-group_concat — Marc Giombetti
– Marc Giombetti, Commented Jun 6, 2013 at 18:26
this may help you w3resource.com/mysql/aggregate-functions-and-grouping/… — Aasif khan
– Aasif khan, Commented Jan 13, 2021 at 12:50

14 revs, 12 users 16% · Accepted Answer · 2020-10-30 05:19:38Z

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

Just be aware of the limitation of 1024 bytes in the resulting column (see parameter group_concat_max_len)
And adding the DISTINCT parameter, you will not get any doubles. ... GROUP_CONCAT(DISTINCT hobbies)
I got the same results even while omitting the GROUP BY person_id clause at the end.

Paul Mougel · Accepted Answer · 2017-09-01 08:13:19Z

127

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

edited Sep 1, 2017 at 8:13

Paul Mougel

17.1k6 gold badges59 silver badges65 bronze badges

answered Nov 10, 2008 at 2:48

lpfavreau

13.3k5 gold badges35 silver badges36 bronze badges

1 Comment

Krzysiek Over a year ago

I think that group by 'all' isn't necessary (moreover unwanted), because this assign to all rows string all and then compare strings between these rows. Am I right?

Vladimir Panteleev · Accepted Answer · 2023-03-17 19:19:48Z

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

Even though this is a good answer, this sounds more like advertising than purely answering. Still a good well-formated answer.

M Khalid Junaid · Accepted Answer · 2013-09-27 14:25:08Z

41

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.

edited Sep 27, 2013 at 14:25

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

answered Apr 8, 2010 at 11:32

pau.moreno

4,7353 gold badges37 silver badges38 bronze badges

Comments

Warren Sergent · Accepted Answer · 2016-06-27 23:03:11Z

31

There's a GROUP Aggregate function, GROUP_CONCAT.

edited Jun 27, 2016 at 23:03

Warren Sergent

2,5974 gold badges37 silver badges44 bronze badges

answered Nov 10, 2008 at 2:48

Dean Rather

32.5k15 gold badges69 silver badges72 bronze badges

Comments

Fedir RYKHTIK · Accepted Answer · 2014-06-10 09:24:31Z

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

Alex Bowyer · Accepted Answer · 2015-04-29 16:35:34Z

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.

Shen liang · Accepted Answer · 2013-09-27 18:47:25Z

19

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

answered Sep 27, 2013 at 18:47

Shen liang

1,44516 silver badges15 bronze badges

2 Comments

jave.web Over a year ago

is it faster than GROUP_CONCAT ?

Jan Over a year ago

I tested this under PHPMyAdmin with MySQL server v5.6.17 on a description column (varchar(50)) in a test table but it returns a BLOB field for each record: SELECT @logmsg := CONCAT_ws(',',@logmsg,description) from test

Oleg Abrazhaev · Accepted Answer · 2019-04-26 09:26:01Z

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.

Jerry Tom · Accepted Answer · 2021-02-24 04:01:42Z

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;

This is what I got : 'Unrecognized statement type. (near "DECLARE" at position 0)'

raghavendra · Accepted Answer · 2018-11-13 13:37:54Z

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

Md. Tarikul Islam Soikot · Accepted Answer · 2021-11-08 19:04:38Z

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 )

Muhammad Shahzad · Accepted Answer · 2021-07-13 06:21:25Z

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:

GROUP_CONCAT(c.name) works which concatenating multiple rows into one column

Payel Senapati · Accepted Answer · 2021-10-04 09:29:34Z

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;

Haddock-san · Accepted Answer · 2022-07-07 20:56:52Z

1

Use GROUP_CONCAT:

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

answered Jul 7, 2022 at 20:56

Haddock-san

9231 gold badge13 silver badges26 bronze badges

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-08-13 17:57:14Z

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

Collectives™ on Stack Overflow

Can I concatenate multiple MySQL rows into one field?

16 Answers 16

3 Comments

1 Comment

Given:

Find:

Solution:

Bonus Solution:

3 Comments

Comments

Comments

Comments

Original Query:

Imploded:

Comments

2 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

16 Answers 16

3 Comments

1 Comment

Given:

Find:

Solution:

Bonus Solution:

3 Comments

Comments

Comments

Comments

Original Query:

Imploded:

Comments

2 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Comments

Linked

Related