Take the 2-minute tour ×

Using MySQL, I can do something like

select hobbies from peoples_hobbies where person_id = 5;

and get:


but instead I just want 1 row, 1 col:

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?

share|improve this question
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 Jun 6 '13 at 18:26
add comment

6 Answers

up vote 498 down vote accepted

You can use GROUP_CONCAT.

As in:

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

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

SET group_concat_max_len = 2048

Of course, you can change 2048 accourding to your needs.

share|improve this answer
this worked perfectly for my situation as well. –  Jason Miesionczek Jun 3 '11 at 19:53
Just be aware of the limitation of 1024 bytes in the resulting column (see parameter group_concat_max_len) –  Dag Mar 28 '12 at 10:09
And adding the DISTINCT parameter, you will not get any doubles. ... GROUP_CONCAT(DISTINCT hobbies) –  Ludwig Jun 18 '12 at 14:39
My need was to get ALL the data from one column. Don't use the GROUP BY clause to do this. Example : SELECT GROUP_CONCAT(emails SEPARATOR ', ') FROM users; –  Jonathan Bergeron Jan 17 at 14:04
add comment

Have a look at group_concat if your MySQL version (4.1) supports it. See http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat for more details.

It would look something like:

select group_concat(hobbies separator ', ') 
  from peoples_hobbies where person_id = 5 group by 'all';
share|improve this answer
Heh, 3 correct exactly the same answers all in the same minute. amazing. –  Dean Rather Nov 10 '08 at 2:51
add comment

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.

share|improve this answer
add comment

There's a GROUP Aggregate function, GROUP_CONCAT.

Edit: I was writing this answer when I was caught-short by a yellow bar popping up at the top telling me someone else just posted the exact same answer. He is winner :)

share|improve this answer
add comment

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

share|improve this answer
is it faster than GROUP_CONCAT ? –  jave.web Feb 6 at 18:39
good question, i didn't test the speed. –  Dreamer Feb 7 at 15:40
and arent you considering of making a speed test for these two ? :) –  jave.web Feb 7 at 18:13
add comment

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 :

share|improve this answer
add comment

protected by Community Aug 31 '11 at 1:54


Would you like to answer one of these unanswered questions instead?
