Comma Separated values

-- Sample Table 

create table #Emp(Name varchar(10),Skills varchar(max))

insert into #Emp values('Ramesh','Hadoop,SQL,DBA')
insert into #Emp values('Arjun','SQL,MSBI')
insert into #Emp values('Mohan','Java')

select * from #Emp

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

-- Code to Display below output

Name  Skill
---------------------
Arjun  MSBI
Arjun  SQL
Mohan  Java
Ramesh  DBA
Ramesh  Hadoop
Ramesh  SQL


SELECT DISTINCT Name,LTRIM(RTRIM(i.value('.', 'VARCHAR(MAX)'))) AS SKILL into #sample
FROM
(
SELECT Name,Skills
,CAST('' + REPLACE(Skills, ',', '') + '' AS XML) AS Des
FROM #Emp
) List
CROSS APPLY Des.nodes('//i') x(i)


select * from #sample 

-- for the above output, Code to Display below output

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

SELECT Distinct Name
,STUFF((SELECT ','+SKILL FROM #sample f where f.Name=s.Name FOR XML PATH('')),1,1,'') AS Skills
from #sample s