SQL Server 2012:Generate column values Dynamically based on concatenation of other column values

Question

I need to generate common backup codes based on the available backup products across the table. Below is the backup product table

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

Here the backup relationship is bidirectional. HP is a backup to DELL SERIES and vice versa. we need to create a common backup code for each product. This backup code is created by considering all backup combinations(recursions). The backup code is a concatenation of the pdt_code of all the backups.Below is the logic: For HP the backup is DELL SERIES. But DELLSERIES has got LENOVO NEW ,DELL SERIES GEN1,DELL SERIES GEN1 NEW as backups. So the backup code for HP is: HP+DS+LN+DG+DN = HPDSLNDGDN

For DELL SERIES the backup is LENOVO NEW(Row 3). But from Row 4,5 we also have backups DELL SERIES GEN1,DELL SERIES GEN1 NEW. Also DELL SERIES itself is a backup of HP(Row 1) So the backup code for DELL SERIES is same as above(as all are involved, order doesnt matter) = HPDSLNDGDN

similarly we need to generate the backup_code for all other products dynamically. Backup code order of concatenation of codes doesnt matter.

Note: For Apple the backup code wil be just AP ad as both product and bkp_product are same. I am using SQL Server 2012.

`` Below is the expect result:

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

Please help me in generating the code dynamically.

Thanks very much.

Possible duplicate of [How to find all connected subgraphs of an undirected graph](https://stackoverflow.com/questions/35254260/how-to-find-all-connected-subgraphs-of-an-undirected-graph) — Vladimir Baranov, Sep 15 '17 at 04:48

score 0 · Answer 1 · answered Sep 26 '17 at 09:44

@vladimir-baranov is correct, I believe. Here is a query that produces an answer. However, the results do not agree with your stated expected results.

I think your expected results are incorrect given the source data. For example, in row 9 you have a generated code of ACAPSOTO. But there is no row with PDT_CODE = 'TO', so the graph breaks down.

Equally, since there is a relationship from DS to AC (line 7) most backup codes will eventually involve 'ACAP'.

Anyway - here you go. I've started with your table generation code. Then it's almost a straight cut/paste from @Vladimir's solution, with a join at the end back to your source table.

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

And the results:

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

SQL Server 2012:Generate column values Dynamically based on concatenation of other column values

1 Answers1