Dean Rather Dean Rather - 1 year ago 74
SQL Question

Can I concatenate multiple MySQL rows into one field?


, 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
functions accept result sets, so does anyone here know how to do this?

che che
Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download