我有一个表,有一个名为“locations”的列,其类型为varchar(max).它带有逗号分隔的代码串,例如’1,3,4′.另一方面,我有一张表将这些代码映射到某些位置,例如
1 -- British 2 -- New Zealand 3 -- Hong Kong 4 -- Taiwan
我的问题是,我正在制作一个VIEW,它将列“位置”的内容映射并替换为相应的逗号分隔文本,例如： ‘1,4’将是’英国,香港,台湾’
这对公司项目来说真的很紧迫,敬请劝告.
谢谢！
问候,
威廉
解决方法
可能有更简单的解决方案,但这是一种方法.
表结构
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,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>
