Comma Separated Value in SQL Query

 

--- create Table

create table Tbl_Exp(Id int identity(1,1) primary key,Emp_Name varchar(50),Working_Day varchar(5))

-- insert some test data

insert into Tbl_Exp values('Adi','M')

insert into Tbl_Exp values('Adi','T')

insert into Tbl_Exp values('Adi','W')

insert into Tbl_Exp values('Adi','TH')

insert into Tbl_Exp values('Pavan','F')

insert into Tbl_Exp values('Pavan','S')

insert into Tbl_Exp values('Pavan','M')

insert into Tbl_Exp values('Madhan','T')

insert into Tbl_Exp values('Madhan','W')

select * from Tbl_Exp

--how to use stuff [to replace some data], by using below query we are going to Replace 1st 2 letters with "***"

select stuff('abcdef',1,2,'***')

-- How to get comma separated value using XML path

Select ','+Working_Day from Tbl_Exp for xml path('')

-- how to remove 1 st values using stuff

select stuff((Select ','+Working_Day from Tbl_Exp for xml path('')),1,1,'')

-- Final Query

select distinct Emp_Name,

(select stuff((Select ','+Working_Day from Tbl_Exp as a where a.Emp_Name=b.Emp_Name for xml path('')),1,1,'') as a) as 'comma_separated'

from Tbl_Exp as b