Combine Multiple Columns And Records In MS SQL Server

Combine Multiple Columns And Records Into Comma Separated One Column In MS SQL Server. 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