Example for STUFF and FOR XML PATH Functions ?

Q. How to group String data IN SQL SERVER?

I have Department table Bellow I want to group by Department_id and display Employee names separated by, how to do this.

   

    Current Table data                                    

Department_id	Emp_Name
10	Krishna
10	Roopa
10	Alice
11	Ben
11	Mark
11	Jesica
12	Radika
12	Lathish
12	Kera

Expected Result

Department_id	Emp_Name
10	Krishna,Roopa,Alice
11	Ben,Mark,Jesica
12	Radika,Lathish,Kera

Using STUFF and for Xml path function we can achieve this result

we are using same table 2 times in where clause.

Query:- 

select t1.Department_id, STUFF(( select ','+ t2.Emp_Name from Department t2

where t2.Department_id = t1.Department_id

for Xml path ('')),1,1,'') as Emp_name

from Department t1

Group by t1.Department_id

Here is the Data to create Table:-

create table Department (Department_id int, Emp_Name varchar(20))

Go

Insert into Department values ( 10, 'Krishna')

Insert into Department values ( 10, 'Roopa')

Insert into Department values ( 10, 'Alice')

Insert into Department values ( 11, 'Ben')

Insert into Department values ( 11, 'Mark')

Insert into Department values ( 11, 'Jesica')

Insert into Department values ( 12, 'Radika')

Insert into Department values ( 12, 'Lathish')

Insert into Department values ( 12, 'Kera')