SQl Task

Table 1 : Employee

Table 2 : Region

Solution 1:

Select *  into #Temp FROM Employee A CROSS APPLY dbo.SplitString(A.REGIONCODE,',')

select a.ID,a.Name,b.RegionName into #temp1 from #Temp a, Region b where a.Item =b.RegionCode

select distinct ID, Name ,STUFF((Select ','+RegionName from #Temp1 T1
where T1.ID=T2.ID FOR XML PATH('')),1,1,'') as [Region Name] from #Temp1 T2

Solution 2:

SELECT y.ID, y.Name,x.RegionCode, x.RegionName  FROM

( SELECT CAST(a.RegionCode as varchar(10)) + ',' + CAST(b.RegionCode as varchar(10))     [RegionCode],
 a.Description+ ',' + b.Description [RegionName]
 FROM Region a CROSS JOIN  Region b   )  x
 INNER JOIN
 (SELECT ID, Name, RegionCode FROM Employee) y on x.RegionCode =y.RegionCode

ID Name Region Name
1 aaa         Chennai,Guindy
2 bbb         Chennai,Tambaram
3 ccc         Guindy,Tambaram
4 ddd         Tambaram,Saidapet

	Employee				Region				Result
EmpID	EmpName	RegionCode		RegionCode	Description		EmpID	EmpName	RegionCode
101	aaa	201, 202		201	Chennai		101	aaa	Chennai, Erode
102	bbb	201, 203		202	Erode		102	bbb	Chennai,Cuddalore
103	ccc	201, 204		203	Cuddalore		103	ccc	Chennai, Trichy
104	ddd	202, 203		204	Trichy		104	ddd	Erode, Cuddalore
105	eee	203, 204					105	eee	Cuddalor, Trichy