Can I concatenate multiple MySQL rows into one field?

1910

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.

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.

group-concat