SQL Server 2012：根据其他列值的串联动态生成列值。

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。 但是DELL SERIES有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的备份(第1行) 因此，DELL SERIES的备份代码与上面相同(因为所有都涉及，顺序不重要)=HPDSLNDGDN

类似地，我们需要为所有其他产品动态生成备用代码。备份代码串联的顺序不重要。

备注： 对于Apple，备份代码将只是AP，因为产品和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

请帮我动态生成代码。

非常感谢。

- SQLGuy2012

---

可能是如何找到无向图的所有连通子图的重复问题。 - Vladimir Baranov

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

- Brett