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;

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?

Answer 1

You can use GROUP_CONCAT.

As in:

select person_id, group_concat(hobbies separator ', ')
    from peoples_hobbies group by person_id;

Answer 2

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

Answer 3

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

Answer 4

Thanks for your answer, that's why i'm looking for. But i have another problem, is that GROUP_CONCAT is limited to 1ko. And i need more.

How can i use more than 1ko for GROUP_CONCAT ???

Answer 5

You could use php's implode and explode functions

to "implode":

<?php
$array = array('shopping', 'fishing', 'coding'); // filling an array
$comma_separated = implode(",", $array); // imploding the array
echo $comma_separated; // lastname,email,phone
?>

to "explode":

<?php

$array(explode('lastname,email,phone', $str, 2));

?>

or go here to see more about the implode and explode functions on php.net

Answer 6

you have saved my life GROUP_CONCAT!

Answer 7

I think you can use GROUP BY key word, coz GROUP CONCAT is aggregate function

Answer 8

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.

Answer 9

karaka bixo, u not belive nisso, passei o dia seeking for this code! congratulations for all!!

Answer 10

hello, I have a problem with GROUP_CONCAT, that return me

12 [BLOB - 1,0  Kio]