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 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?
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable. In this method, you don't need to worry about the trailing comma.
In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
You can use GROUP_CONCAT
:
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ') FROM peoples_hobbies GROUP BY person_id;
As Ludwig stated in his comment, 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, 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, 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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With