With MySQL, I can do similar things:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

My output:

shopping
fishing
coding

But I just want one row and one column:

Expected production:

shopping, fishing, coding

The reason is that I have to select multiple values from multiple tables, and after all joins, I get a lot more rows than I want.

I am here MySQL Doc Found a function on that doesn't look like the CONCAT or CONCAT? WS functions accept result sets, so does anyone here know how to do this?

#1 building

Use MySQL (5.6.13) session variables and assignment operators as follows

SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;

Then you can get

test1,test11

#2 building

In my case, I have a row of Ids and must cast it to char, otherwise the result will be encoded in binary format:

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

#3 building

You can change the maximum length of the group concat value by setting the group concat Max len parameter.

see also In MySQL documents Details.

#4 building

Alternate syntax to connect multiple separate lines

Warning: This article will make you hungry.

In view of:

I found myself wanting to select multiple individual rows (rather than a group) and connect to a field.

Suppose you have a table of product ID s and their names and prices:

+------------+--------------------+-------+
| product_id | name               | price |
+------------+--------------------+-------+
|         13 | Double Double      |     5 |
|         14 | Neapolitan Shake   |     2 |
|         15 | Animal Style Fries |     3 |
|         16 | Root Beer          |     2 |
|         17 | Lame T-Shirt       |    15 |
+------------+--------------------+-------+

Then, you'll have some fancy ajax and make these puppies a check box.

You are hungry. Hippo users choose 13, 15, 16. No dessert for her today

Look for:

A method of summarizing user orders in a row using pure mysql.

Solution:

Use group? Concat and IN clause :

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);

Which output:

+------------------------------------------------+
| order_summary                                  |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+

Reward solution:

If you also want the total price, please throw SUM() :

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary                                  | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer |    10 |
+------------------------------------------------+-------+

PS: I'm sorry if you don't have one nearby In-N-Out ...

#5 building

You can use the GROUP_CONCAT :

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

As Ludwig was As stated in its comments, You can add the DISTINCT operator to avoid repetition:

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

Just like Jan is in. As stated in their comments, You can also sort values before using ORDER BY blasting:

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

Just as Dag was His comments The result is limited to 1024 bytes. To fix this problem, run the following query before the query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 as needed. To calculate and assign values:

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