如何获取一串逗号分隔代码对应的逗号分隔文本?

问题描述

我有一张表，有一列名为locations"的列是 varchar(max) 类型.它携带一串逗号分隔的代码，例如'1, 3, 4'.另一方面，我有一个表格将这些代码映射到某些位置，例如

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

我的问题是，我正在制作一个视图，它将位置"列的内容映射并替换为相应的逗号分隔文本，例如'1, 3, 4' 将是 '英国、香港、台湾'

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.

谢谢！

问候，威廉

推荐答案

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

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

表结构

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>

这篇关于如何获取一串逗号分隔代码对应的逗号分隔文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！