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

i've table, there column called "locations" in type of varchar(max). carries string of comma-separated code, e.g. '1, 3, 4'. on other hand, i've table map these code locations, e.g.

1 -- british 2 -- new zealand 3 -- hong kong 4 -- taiwan 

my problem is, i'm making view map , replace content of column "locations" corresponding comma-separated text, e.g. '1, 3, 4' 'british, hong kong, taiwan'

this urgent company project, please kindly advise.

thank you!

regards, william

there might simpler solutions here 1 way.

table structure

create table locations(locationid int, location varchar(50)) create table othertable(id int, locations varchar(max)) 

test data

insert locations values(1, 'location <1>') insert locations values(2, 'location <2>') insert locations values(3, 'location <3>') insert locations values(4, 'location <4>') insert locations values(5, 'location <5>')  insert othertable values (1, '') insert othertable values (2, '2') insert othertable values (3, '1, 3 ,5') 

query

;with cte (   select      t.id,     coalesce(l.location, '') location   othertable t     cross apply       (select cast('<r>'+replace(t.locations, ',', '</r><r>')+'</r>' xml)) locxml(xmlcol)     cross apply       locxml.xmlcol.nodes('r') locid(idcol)      left outer join locations l       on l.locationid = locid.idcol.value('.', 'int') ) select   c1.id,   stuff((select ', '+c2.location          cte c2           c1.id = c2.id          xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') locations cte c1 group c1.id 

result

id  locations --- ---------------------------------------- 1    2   location <2> 3   location <1>, location <3>, location <5>