asked
SQL Table column values side by side in one column
I have one table ManagerID|EmpID ------------------ M01 | 100 | ------------------ M01 | 101 | ------------------ M02 | 102 | ------------------ M02 | 103 | ------------------ M02 | 104 | ------------------ M03 | 105 | ------------------ M04 | 106 | ------------------ I need it to look like the following: ManagerID| EmpID ------------------------- M01 | 100,101 | ------------------------- M02 | 102,103,104 | ------------------------- M03 | 105 | ------------------------- M04 | 106 | -------------------------
You can use FOR XML PATH in a correlated subquery to create a comma separated list. CREATE TABLE #emp( ManagerID CHAR(3),EmpID INT); INSERT #emp (managerid,empid) values ('M01',100), ('M01',101), ('M02',102), ('M02',103), ('M02',104), ('M03',105), ('M04',106); SELECT DISTINCT Managerid, STUFF((SELECT ',' + CAST(empid AS VARCHAR(3)) FROM #emp e2 WHERE e2.managerid=e.managerid FOR XML PATH('')),1,1,'') FROM #emp e
...and from SQL Server 2017 you can use the STRING_AGG aggregate function to accomplish the same. Something like this: SELECT ManagerID, STRING_AGG(CAST(empid as varchar(3)),',') FROM #emp GROUP BY ManagerID;
21 People are following this question.
