SQL 服务器 2012 :Generate column values Dynamically based on concatenation of other column values

Question

我需要根据整个表格中可用的备份产品生成通用备份代码。下面是备份产品表

if object_id('tempdb..#pdt') is not null
DROP TABLE #pdt
create table #pdt(ROW_NUM INT IDENTITY(1,1),  PRODUCT NVARCHAR(30), PDT_CODE 
NVARCHAR(10),BKP_PRODUCT   NVARCHAR(30),BKP_PDT_CODE NVARCHAR(10))

INSERT #pdt( PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
VALUES ('HP','HP','DELL SERIES','DS'),('HP', 'HP','LENOVO NEW','LN'),
('DELL SERIES','DS','LENOVO NEW','LN'),
('DELL SERIES','DS','DELL SERIES GEN1','DG'),
('DELL SERIES','DS','DELL SERIES GEN1 NEW','DN'),
('SONY','SO','TOSHIBA','TO'),
('SONY','DS','ACER','AC')

 INSERT #pdt(PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
 VALUES ('APPLE','AP','APPLE','AP') 
 INSERT #pdt(PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
 VALUES ('ACER','AC','APPLE','AP') 

 SELECT * FROM #pdt

ROW_NUM     PRODUCT   PDT_CODE   BKP_PRODUCT   BKP_PDT_CODE
 ----------- ------------------------------ ---------- ---------------------
 --------- ------------
 1           HP          HP         DELL SERIES         DS
 2           HP          HP         LENOVO NEW          LN
 3           DELL SERIES DS         LENOVO NEW          LN
 4           DELL SERIES DS         DELL SERIES GEN1    DG
 5           DELL SERIES DS         DELL SERIES GEN1 NEW  DN
 6           SONY        SO         TOSHIBA             TO
 7           SONY        DS         ACER                AC
 8           APPLE       AP         APPLE               AP
 9           ACER        AC         APPLE               AP              

这里的备份关系是双向的。 HP 是 DELL SERIES 的备份，反之亦然。我们需要为每个产品创建一个通用的备份代码。此备份代码是通过考虑所有备份组合(递归)创建的。备份代码是所有备份的pdt_code的串联。逻辑如下:对于 HP，备份是 DELL SERIES。但是DELLSERIES有LENOVO NEW,DELL SERIES GEN1,DELL SERIES GEN1 NEW作为备份。所以HP的备份码是:HP+DS+LN+DG+DN = HPDSLNDGDN

对于 DELL SERIES，备份是 LENOVO NEW(第 3 行)。但是从第 4,5 行我们还有备份 DELL SERIES GEN1，DELL SERIES GEN1 NEW。DELL SERIES本身也是HP的备份(Row 1)所以DELL SERIES的备份代码和上面一样(因为都涉及，顺序无关紧要)= HPDSLNDGDN

同样，我们需要为所有其他产品动态生成备份代码。代码串联的备用代码顺序无关紧要。

注意:对于 Apple，备份代码将只是 AP 广告，因为 product 和 bkp_product 是相同的。我正在使用 SQL Server 2012。

`` 以下是预期结果:

ROW_NUM  PRODUCT           PDT_CODE BKP_PRODUCT     BKP_PDT_CODE BACKUP_CODE
----------- ------------------------------ ---------- ----------------------
1        HP                HP       DELL SERIES              DS HPDSLNDGDN
2        HP                HP       LENOVO NEW               LN HPDSLNDGDN
3        DELL SERIES       DS       LENOVO NEW               LN HPDSLNDGDN
4        DELL SERIES       DS       DELL SERIES GEN1         DG HPDSLNDGDN
5        DELL SERIES       DS       DELL SERIES GEN1 NEW     DN HPDSLNDGDN
6        SONY              SO       TOSHIBA                  TO SOTOACAP
7        SONY              DS       ACER                     AC SOTOACAP
8        APPLE             AP       APPLE                    AP AP
9        ACER              AC       APPLE                    AP ACAPSOTO

请帮助我动态生成代码。

非常感谢。

Answer 1

@vladimir-baranov 是正确的，我相信。这是一个产生答案的查询。但是，结果与您声明的预期结果不一致。

鉴于源数据，我认为您的预期结果不正确。例如，在第 9 行中，您有一个生成代码 ACAPSOTO。但是没有 PDT_CODE = 'TO' 的行，因此图表不完整。

同样，由于存在从 DS 到 AC 的关系(第 7 行)，大多数备用代码最终将涉及“ACAP”。

无论如何 - 给你。我已经开始使用您的表格生成代码。然后它几乎是从@Vladimir 的解决方案中直接剪切/粘贴，在末尾连接回到您的源表。

if object_id('tempdb..#pdt') is not null
DROP TABLE #pdt
create table #pdt(ROW_NUM INT IDENTITY(1,1),  PRODUCT NVARCHAR(30), PDT_CODE 
NVARCHAR(10),BKP_PRODUCT   NVARCHAR(30),BKP_PDT_CODE NVARCHAR(10))

INSERT #pdt( PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
VALUES ('HP','HP','DELL SERIES','DS'),('HP', 'HP','LENOVO NEW','LN'),
('DELL SERIES','DS','LENOVO NEW','LN'),
('DELL SERIES','DS','DELL SERIES GEN1','DG'),
('DELL SERIES','DS','DELL SERIES GEN1 NEW','DN'),
('SONY','SO','TOSHIBA','TO'),
('SONY','DS','ACER','AC')

 INSERT #pdt(PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
 VALUES ('APPLE','AP','APPLE','AP') 
 INSERT #pdt(PRODUCT,PDT_CODE, BKP_PRODUCT,BKP_PDT_CODE )
 VALUES ('ACER','AC','APPLE','AP') 


 ;
 WITH
CTE_Idents
AS
(
    SELECT PDT_CODE AS Ident
    FROM #pdt

    UNION

    SELECT BKP_PDT_CODE AS Ident
    FROM #pdt
)
,CTE_Pairs
AS
(
    SELECT PDT_CODE as Ident1, BKP_PDT_CODE as Ident2
    FROM #pdt
    WHERE PDT_CODE <> BKP_PDT_CODE

    UNION

    SELECT BKP_PDT_CODE AS Ident1, PDT_CODE AS Ident2
    FROM #pdt
    WHERE PDT_CODE <> BKP_PDT_CODE
)
,CTE_Recursive
AS
(
    SELECT
        CAST(CTE_Idents.Ident AS varchar(8000)) AS AnchorIdent 
        , Ident1
        , Ident2
        , CAST(',' + Ident1 + ',' + Ident2 + ',' AS varchar(8000)) AS IdentPath
        , 1 AS Lvl
    FROM 
        CTE_Pairs
        INNER JOIN CTE_Idents ON CTE_Idents.Ident = CTE_Pairs.Ident1

    UNION ALL

    SELECT 
        CTE_Recursive.AnchorIdent 
        , CTE_Pairs.Ident1
        , CTE_Pairs.Ident2
        , CAST(CTE_Recursive.IdentPath + CTE_Pairs.Ident2 + ',' AS varchar(8000)) AS IdentPath
        , CTE_Recursive.Lvl + 1 AS Lvl
    FROM
        CTE_Pairs
        INNER JOIN CTE_Recursive ON CTE_Recursive.Ident2 = CTE_Pairs.Ident1
    WHERE
        CTE_Recursive.IdentPath NOT LIKE CAST('%,' + CTE_Pairs.Ident2 + ',%' AS varchar(8000))
)
,CTE_RecursionResult
AS
(
    SELECT AnchorIdent, Ident1, Ident2
    FROM CTE_Recursive
)
,CTE_CleanResult
AS
(
    SELECT AnchorIdent, Ident1 AS Ident
    FROM CTE_RecursionResult

    UNION

    SELECT AnchorIdent, Ident2 AS Ident
    FROM CTE_RecursionResult
)
SELECT
    CTE_Idents.Ident
    ,CASE WHEN CA_Data.XML_Value IS NULL 
    THEN CTE_Idents.Ident ELSE CA_Data.XML_Value END AS GroupMembers
    ,DENSE_RANK() OVER(ORDER BY 
        CASE WHEN CA_Data.XML_Value IS NULL 
        THEN CTE_Idents.Ident ELSE CA_Data.XML_Value END
    ) AS GroupID
into #Groups
FROM
    CTE_Idents
    CROSS APPLY
    (
        SELECT CTE_CleanResult.Ident+','
        FROM CTE_CleanResult
        WHERE CTE_CleanResult.AnchorIdent = CTE_Idents.Ident
        ORDER BY CTE_CleanResult.Ident FOR XML PATH(''), TYPE
    ) AS CA_XML(XML_Value)
    CROSS APPLY
    (
        SELECT CA_XML.XML_Value.value('.', 'NVARCHAR(MAX)')
    ) AS CA_Data(XML_Value)
WHERE
    CTE_Idents.Ident IS NOT NULL
ORDER BY Ident;

select #pdt.*, 
        case 
        when #pdt.PDT_CODE = #pdt.BKP_PDT_CODE then #pdt.PDT_CODE 
        else replace(#Groups.GroupMembers, ',', '') end BACKUP_CODE
from #pdt
    join #Groups
    on #pdt.PDT_CODE = #Groups.Ident

结果:

ROW_NUM     PRODUCT         PDT_CODE   BKP_PRODUCT            BKP_PDT_CODE BACKUP_CODE
----------- --------------- ---------- ---------------------- ------------ --------------
1           HP              HP         DELL SERIES            DS           ACAPDGDNDSHPLN
2           HP              HP         LENOVO NEW             LN           ACAPDGDNDSHPLN
3           DELL SERIES     DS         LENOVO NEW             LN           ACAPDGDNDSHPLN
4           DELL SERIES     DS         DELL SERIES GEN1       DG           ACAPDGDNDSHPLN
5           DELL SERIES     DS         DELL SERIES GEN1 NEW   DN           ACAPDGDNDSHPLN
6           SONY            SO         TOSHIBA                TO           SOTO
7           SONY            DS         ACER                   AC           ACAPDGDNDSHPLN
8           APPLE           AP         APPLE                  AP           AP
9           ACER            AC         APPLE                  AP           ACAPDGDNDSHPLN