How to Combine Multiple Rows into a Single Comma-Separated Row in MySQL?

MySQL: Converting Multiple Rows into a Single Comma-Separated Row

Within a MySQL database, you may encounter a situation where you want to condense multiple rows into a single row, with specific columns represented as comma-separated values. This can be achieved by utilizing the GROUP_CONCAT function, as demonstrated in the following example scenario.

Example Scenario:

Consider two tables: DISH and DISH_HAS_DISHES, where DISH holds all available dishes, and DISH_HAS_DISHES defines a one-to-many relationship between DISHes. The DISH table has the following structure:

DISH:
dish_id   dish_name
1         dish_1
2         dish_2
3         dish_3
4         dish_4

While the DISH_HAS_DISHES table looks like this:

DISH_HAS_DISHES:
meal_id   dish_id
1         2
1         3
1         4

Objective:

Our aim is to transform the data into a format where each meal has a corresponding row, listing its dish IDs and names as comma-separated values. The desired output is:

meal_id     dish_ids     dish_names
1           2,3,4        dish_2, dish_3, dish_4

Solution:

To achieve this, we will use the GROUP_CONCAT function, along with some straightforward JOIN operations:

SELECT m.meal_id, 
        GROUP_CONCAT(dish_id) AS dish_ids, 
        GROUP_CONCAT(dish_name) AS dish_names
FROM DISH_HAS_DISHES m JOIN DISH d ON (m.dish_id = d.dish_id)
GROUP BY meal_id

In this query:

• We start by joining the DISH_HAS_DISHES table (m) with the DISH table (d) on the dish_id column.
• Within the SELECT statement, we use GROUP_CONCAT to merge multiple values from the dish_id and dish_name columns.
• Finally, we group the results by the meal_id to obtain a single row for each meal.

By executing this query, we will obtain the desired output, where each row represents a meal, its dish IDs, and their corresponding dish names, all represented as comma-separated values. This technique is especially useful when working with tabular data and consolidating information across multiple rows.

The above is the detailed content of How to Combine Multiple Rows into a Single Comma-Separated Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!