Get the concatenated Location Names from concatenated Location Id's

Niladri.biswas
Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 1200
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
*/

Comments or Responses

Login to post response