SQL SELECT query to set values with same id on different columns

userErSg 0
Viewed 52

I currently have a table with two columns: 'id' and 'type'. There can be multiple rows with the same id, but a type cannot be used more than once for each id. Example:

id type
1 type-1
1 type-2
1 type-3
2 type-1
2 type-2

I would like to construct a SELECT query which from the table above, would take every id and output a string containing all the types with the same id. Example:

id types
1 type-1,type-2,type-3
2 type-1,type-2

However, I do not know how I can do this. Can someone help, please?

mysql sql
1 Answers

Use GROUP_CONCAT with an appropriate ORDER BY clause:

SELECT id,
      GROUP_CONCAT(type ORDER BY CAST(SUBSTRING_INDEX(type, '-', -1) AS UNSIGNED)) AS types
FROM yourTable
GROUP BY id
ORDER BY id;
Tim Biegeleisen0
Related