How can I use GROUP_CONCAT to combine multiple rows into a single row with comma-separated values in MySQL?

Extracting Rows as Comma-Separated Values in MySQL: Exploring GROUP_CONCAT Function

The challenge of transforming multiple rows into a single row with comma-separated values in MySQL often arises when dealing with related data from multiple tables. This task can be elegantly accomplished utilizing the powerful GROUP_CONCAT function.

To achieve this goal, consider the example provided. Two tables, DISH and DISH_HAS_DISHES, represent a one-to-many relationship between dishes and meals. The requirement is to create a format that collates dish IDs and names for each meal as comma-separated values.

The solution lies in the GROUP_CONCAT function. This function effectively concatenates values of an expression for each group of rows, producing a single string. In this case, the expression is the combination of dish_id and dish_name attributes.

To transform the data, a query joining the DISH_HAS_DISHES and DISH tables can be constructed:

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

The JOIN statement links the two tables, while GROUP BY groups rows by the meal_id column. For each meal, the GROUP_CONCAT function aggregates dish_id and dish_name values into the dish_ids and dish_names columns, respectively, separated by commas.

As a result, the query produces the desired format: a single row for each meal, with comma-separated dish IDs and names. Utilizing the GROUP_CONCAT function efficiently solves the challenge of extracting multiple rows as a single row with comma-separated values in MySQL. Furthermore, this approach can be applied to various database scenarios, enhancing data retrieval and manipulation capabilities.

The above is the detailed content of How can I use GROUP_CONCAT to combine multiple rows into a single row with comma-separated values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!