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

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?

CodePudding user response：

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;

CodePudding user response：

SELECT STRING_AGG(types,','),id
FROM dbo.yourTable
GROUP BY id