如何将逗号分隔的代码字符串转换为相应的逗号分隔文本？

Question

我有一张表，其中有一个叫做“locations”的列，它的类型是varchar(max)。它包含逗号分隔的代码字符串，例如'1, 3, 4'。另一方面，我有一张表将这些代码映射到一些位置，例如：

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

我的问题是，我正在创建一个VIEW，它将映射并替换“locations”列的内容为相应的逗号分隔文本，例如'1, 3, 4'将变为'英国，香港，台湾'

这对公司项目来说非常紧急，请您给予指导。

谢谢！

祝好， William

- William X

Answer 1

可能有更简单的解决方案，但这里是一种方法。

表结构

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

测试数据

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')

查询

;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

结果

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

- Mikael Eriksson