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.