sql server 2005 - How to get the corresponding comma-separated text for a string of comma separated codes?

Question

I've a table, there is a column called "locations" which is in type of varchar(max). It carries string of comma-separated code, e.g. '1, 3, 4'. On the other hand, I've a table which map these code to some locations, e.g.

1 -- British
2 -- New Zealand
3 -- Hong Kong
4 -- Taiwan

My problem is, I'm making a VIEW which will map and replace the content of the column "locations" to the corresponding comma-separated text, e.g. '1, 3, 4' will be 'British, Hong Kong, Taiwan'

This is really urgent for the company project, please kindly advise.

Thank you!

Regards, William

Answer 1

There might be simpler solutions to do this but here is one way.

Table structure

create table Locations(LocationID int, Location varchar(50))
create table OtherTable(ID int, Locations varchar(max))

Test data

insert into Locations values(1, 'Location <1>')
insert into Locations values(2, 'Location <2>')
insert into Locations values(3, 'Location <3>')
insert into Locations values(4, 'Location <4>')
insert into Locations values(5, 'Location <5>')

insert into OtherTable values (1, '')
insert into OtherTable values (2, '2')
insert into OtherTable values (3, '1, 3 ,5')

Query

;with cte as
(
  select 
    T.ID,
    coalesce(L.Location, '') as Location
  from OtherTable as T
    cross apply
      (select cast('<r>'+replace(T.Locations, ',', '</r><r>')+'</r>' as xml)) LocXML(XMLCol)
    cross apply
      LocXML.XMLCol.nodes('r') LocID(IDCol) 
    left outer join Locations as L
      on L.LocationID = LocID.IDCol.value('.', 'int')
)
select
  C1.ID,
  stuff((select ', '+C2.Location
         from cte as C2 
         where C1.ID = C2.ID
         for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as Locations
from cte as C1
group by C1.ID

Result

ID  Locations
--- ----------------------------------------
1   
2   Location <2>
3   Location <1>, Location <3>, Location <5>