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;

**My Output:**

shopping
fishing
coding

but instead I just want 1 row, 1 col:

**Expected Output:**

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 <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws">MySQL Doc</a> and it doesn't look like the `CONCAT` or `CONCAT_WS` functions accept result sets.

So does anyone here know how to do this?

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][1].


[1]:

[To see links please register here]

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

test1,test11

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 :

SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table

I had a more complicated query, and found that I had to use `GROUP_CONCAT` in an outer query to get it to work:

Original Query:
---------------

SELECT DISTINCT userID
FROM event GROUP BY userID
HAVING count(distinct(cohort))=2);

Imploded:
---------

SELECT GROUP_CONCAT(sub.userID SEPARATOR ', ')
FROM (SELECT DISTINCT userID FROM event
GROUP BY userID HAVING count(distinct(cohort))=2) as sub;


Hope this might help someone.

There's a GROUP Aggregate function, <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat">GROUP_CONCAT</a>.

Have a look at `GROUP_CONCAT` if your MySQL version (4.1) supports it. See [the documentation][1] for more details.

It would look something like:

SELECT GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
WHERE person_id = 5
GROUP BY 'all';


[1]:

[To see links please register here]

we have two way to concatenate columns in MySql

select concat(hobbies) as `Hobbies` from people_hobbies where 1
Or

select group_concat(hobbies) as `Hobbies` from people_hobbies where 1

For somebody looking here how to use `GROUP_CONCAT` with subquery - posting this example
```
SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid
```
So `GROUP_CONCAT` must be used inside the subquery, not wrapping it.

You can use [`GROUP_CONCAT`][group-concat]:

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

As Ludwig stated in [his comment,][ludwig] you can add the `DISTINCT` operator to avoid duplicates:

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

As Jan stated in [their comment,][jan] you can also sort the values before imploding it using `ORDER BY`:

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

As Dag stated in [his comment,][dag] there is a 1024 byte limit on the 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. To calculate and assign the value:


SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);

[group-concat]:

[To see links please register here]

[dag]:

[To see links please register here]

[ludwig]:

[To see links please register here]

[jan]:

[To see links please register here]