Dean Rather Dean Rather - 5 months ago 16
SQL Question

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;


and get:

shopping
fishing
coding


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?

che che
Answer

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 according to your needs.