May 20, 2013 11:55 by
Scott
In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.
Here is the scenario
I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.
My table schema is shown in the image below
And this is Data into table
I want output in following format
Department FirstName
IT amiT,Emp1,Emp5
Admin Shobhit, Emp3,Emp7
and so on
To get this desired result we need to write below mentioned query
1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1
And the output of this SQL Query would be