U can do it in two ways... Either Using
Fnsplitter
Function or joins...
Create Table #Temp
(
Id int,
Gid Nvarchar(40),
Sid Nvarchar(40)
)
Create Table #Dept
(
Gid Nvarchar(40),
Gname Nvarchar(40)
)
Insert into #Temp
Select 1,'1,3','101' Union all
Select 2,'3','102' Union all
Select 3,'1,2,3','103'
Insert into #Dept
Select '1','dance' Union All
Select '2','cricket' Union All
Select '3','football'
SELECT e.Sid, STUFF((SELECT ',' + d.Gname FROM #Dept AS d
INNER JOIN #Temp AS t
ON ',' + t.Gid + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.Gid) + ',%'
WHERE t.Gid = e.Gid
ORDER BY Gname
FOR XML PATH, TYPE).value('.', 'nvarchar(max)'), 1, 1, '') as Gname
FROM #Temp AS e
ORDER BY Sid;
Drop Table #Temp
Drop Table #Dept
Output:
Sid Gname
101 dance,football
102 football
103 cricket,dance,football