MySQL - Concatenate multiple-row result into one field?
How to concatenate multiple-row result into one field in MySQL?
You have a table orders
| id | table | dish |
|---|---|---|
| 1 | 01 | Fish |
| 2 | 02 | Beef |
| 3 | 13 | Pork |
| 4 | 02 | Salad |
| 4 | 15 | Beef |
And you want to show the result like this: Fish, Beef, Pork, Salad, Beef
You can use GROUP_CONCAT
SELECT table, GROUP_CONCAT(dish SEPARATOR ', ')
FROM orders GROUP BY table;
To avoid duplicate:
SELECT table, GROUP_CONCAT(DISTINCT dish SEPARATOR ', ')
FROM orders GROUP BY table;
And to sort the value
SELECT table, GROUP_CONCAT(dish ORDER BY dish ASC SEPARATOR ', ')
FROM orders GROUP BY table;
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
