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?
Alternate syntax to concatenate multiple, individual rows
WARNING: This post will make you hungry.
Given:
I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.
Let's say you have a table of product ids and their names and prices:
Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.
Your hungry-hippo user selects
13, 15, 16
. No dessert for her today...Find:
A way to summarize your user's order in one line, with pure mysql.
Solution:
Use
GROUP_CONCAT
with the theIN
clause:Which outputs:
Bonus Solution:
If you want the total price too, toss in
SUM()
:PS: Apologies if you don't have an In-N-Out nearby...
Have a look at
GROUP_CONCAT
if your MySQL version (4.1) supports it. See the documentation for more details.It would look something like:
You can change the max length of the
GROUP_CONCAT
value by setting thegroup_concat_max_len
parameter.See details in the MySQL documantation.
There's a GROUP Aggregate function, GROUP_CONCAT.
You can use
GROUP_CONCAT
:As Ludwig stated in his comment, you can add the
DISTINCT
operator to avoid duplicates:As Jan stated in their comment, you can also sort the values before imploding it using
ORDER BY
:As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
Of course, you can change
2048
according to your needs. To calculate and assign the value:we have two way to concatenate columns in MySql
Or