Get the concatenated Location Names from concatenated Location Id's

Hi,there has been a recent requirement in the project where we have been presented with two input tables as under

(A) Location table

LocationId	LocationName
1	Bangalore
2	Chennai
3	Kolkata
4	Hydrabad

(B) LocationMerge table

ConcatenatedLocationId
1,2
1,2,3
1,4
2,3

The expected output being the Concatenated Location Names as under

ConcatenatedLocationId	ConcatenedLocationNames
1,2	Bangalore,Chennai
1,2,3	Bangalore,Chennai,Kolkata
1,4	Bangalore,Hydrabad
2,3	Chennai,Kolkata

Solution

/* Test Environment*/
--Location table
Declare @tblLocations table(LocationId int identity(1,1),LocationName Varchar(50))
Insert Into @tblLocations 
		Select 'Bangalore' Union All Select 'Chennai' Union All
		Select 'Kolkata' Union All Select 'Hydrabad'

--Location Merge table
Declare @tblLocationMerge table(ConcatenatedLocationId Varchar(100))
Insert Into @tblLocationMerge 

		Select '1,2' Union All Select '1,2,3' Union All 
		Select '1,4' Union All Select '2,3'

-- Project the records 
Select * From @tblLocations 
Select * From @tblLocationMerge
/* End Test Environment */


/* Program*/
;With SplitDataCTE As
(
	Select 

	x.ConcatenatedLocationId
	,t.SplittedRecord 
	From
	(

		Select 
		*,
		Cast('<X>'+Replace(t.ConcatenatedLocationId,',','</X><X>')+'</X>' As XML) As record
		From @tblLocationMerge t
	)x
	Cross Apply
	( 
		Select fdata.D.value('.','varchar(50)') As SplittedRecord
		From x.record.nodes('X') As fdata(D)
	) t
)
,MergeRecordCTE AS
(
	Select
			s.*,t1.*
	From SplitDataCTE s
	Join @tblLocations t1 On t1.LocationId = s.SplittedRecord
)

Select 
		m1.ConcatenatedLocationId
		,ConcatenedLocationNames = Stuff((
											Select ',' + Cast(m2.LocationName As Varchar(Max))
											From MergeRecordCTE m2
											Where m1.ConcatenatedLocationId = m2.ConcatenatedLocationId
											For Xml Path('')
											),1,1,''
										)
From MergeRecordCTE m1
Group By m1.ConcatenatedLocationId 

/* Result
ConcatenatedLocationId ConcatenedLocationNames
1,2                     Bangalore,Chennai
1,2,3                   Bangalore,Chennai,Kolkata
1,4                     Bangalore,Hydrabad
2,3                     Chennai,Kolkata
*/