How can I Combine Multiple Rows into a Comma-Separated String Using MySQL?

MySQL: Consolidating Multiple Rows into a Comma-Separated String

In MySQL, the GROUP_CONCAT function can be used to merge multiple rows into a single, comma-separated string. This technique is particularly useful when combining data from tables with one-to-many relationships.

Problem Statement

Consider the following tables:

| DISH |
|---|---|
| dish_id | dish_name |
| 1 | dish_1 |
| 2 | dish_2 |
| 3 | dish_3 |
| 4 | dish_4 |

| DISH_HAS_DISHES |
|---|---|
| meal_id | dish_id |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |

The goal is to transform this data into a format where each meal_id is associated with a comma-separated list of dish_ids and dish_names. The desired output is:

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

Solution

The GROUP_CONCAT function provides an efficient way to achieve this consolidation. The following query joins the DISH and DISH_HAS_DISHES tables, groups the results by meal_id, and uses the GROUP_CONCAT function to concatenate the dish_id and dish_name values for each group:

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;

Result

Executing this query will produce the desired output:

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

Conclusion

The GROUP_CONCAT function provides a powerful tool for combining multiple rows into a single string. This technique is particularly useful in scenarios where you need to consolidate data from related tables, such as in the case of one-to-many relationships.

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