用列中的描述替换逗号分隔的主数据

小编典典

用列中的描述替换逗号分隔的主数据

sql

有2个SQL Server表：

产品：

Name               Status Code
------------------------------------
Product 1          1001, 1003
Product 2          1001, 1005, 1006

状态：

Code               Description
------------------------------------
1001               State A
1003               State B
1005               State C
1006               State D
...

我希望得到类似的东西：

Product          Status
---------------------------------------------
Product 1        State A, State B
Product 2        State A, State C, State D

阅读 175

2021-04-15

共1个答案

小编典典

您可以使用如下查询：

**[See working demo](http://rextester.com/ICI68882)**

create table Products (Name varchar(100), [Status Code] varchar(100));
insert into Products values
('Product 1', '1001, 1003')
,('Product 2', '1001, 1005, 1006');
create table [Status] (Code varchar(100),      Description varchar(100));
insert into [Status] values
('1001',   'State A')
,('1003',   'State B')
,('1005',   'State C')
,('1006',   'State D')

; WITH X AS 
(
    SELECT 
     P1.Name,
     S.Description
    FROM
     (
     SELECT *,
     cast('<X>'+replace(P.[Status Code],',','</X><X>')+'</X>' as XML) AS xmlprods FROM Products P
     )P1
     CROSS APPLY
     ( 
     SELECT fdata.D.value('.','varchar(100)') AS splitdata 
     FROM P1.xmlprods.nodes('X') AS fdata(D)) O
     LEFT JOIN [Status] S
     ON S.Code=  LTRIM(RTRIM(O.splitdata ))
    )

SELECT 
    Name,
    Description= STUFF((
  SELECT ',' + Description FROM x AS x2 
  WHERE x2.Name = x.Name
  ORDER BY Name FOR XML PATH, 
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
  FROM 
  X
GROUP BY Name

2021-04-15