MySQL – How to group values from multiple rows in one field
There are situations in which we would like to group values of multiple rows in just one field. Consider this little example in which we have three tables (cars, colors and link table) and we would like to have a query with all the cars in the first column, and all the colors in which tey are available in the second column.
Cars
| IDCar | Name |
|---|---|
| 1 | Audi |
| 2 | Mercedes |
| 3 | Bmw |
Colors
| IDColor | Color |
|---|---|
| 1 | White |
| 2 | Blue |
| 3 | Black |
| 4 | Orange |
Link Table
| IDCar | IDColor |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
| 3 | 2 |
| 3 | 4 |
| 2 | 3 |
| 3 | 1 |
| 1 | 3 |
The final table we want to obtain is something like this:
| IDCar | CarName | AvailableColors |
|---|---|---|
| 1 | Audi | Black, Blue, Orange, White |
| 3 | BMW | Blue, Orange, White |
| 2 | Mercedes | Black |
To achieve this result, you can use a SQL query with the GROUP_CONCAT mysql function:
SELECT
`c`.`IDCar` AS `IDCar`,
`c`.`Name` AS `CarName`,
GROUP_CONCAT(`cl`.`Color`
SEPARATOR ',') AS `AvailableColors`
FROM
((`cars` `c`
JOIN `linktable` `lt` ON ((`c`.`IDCar` = `lt`.`IDCar`)))
JOIN `colors` `cl` ON ((`lt`.`IDColor` = `cl`.`IDColor`)))
in this way you will obtain an unordered list.
If you want:
- Order the list by cars’ name, you will have to add both GROUP BY and ORDER BY clauses to the query:
...
FROM
((`cars` `c`
JOIN `linktable` `lt` ON ((`c`.`IDCar` = `lt`.`IDCar`)))
JOIN `colors` `cl` ON ((`lt`.`IDColor` = `cl`.`IDColor`)))
GROUP BY `c`.`Name`
ORDER BY `c`.`Name`
- Order the colors in the AvailableColors Field, you will have to add the ORDER BY clause in the GROUP_CONCAT function:
GROUP_CONCAT(`cl`.`Color`
ORDER BY `cl`.`Color` ASC
SEPARATOR ',') AS `AvailableColors`
- Select only one occurence of color, in case of multiple occurencies, you will have to add the DISTINCT clause in the GROUP_CONCAT function:
GROUP_CONCAT(DISTINCT `cl`.`Color`
SEPARATOR ',') AS `AvailableColors`