Sairam62cse Sairam62cse - 1 month ago 6
SQL Question

Concatenate multiple rows to single row with condition check using recursive CTE

CREATE TABLE table1
(
num INT,
val VARCHAR(100)
);

INSERT INTO table1
VALUES (1, 'test1'), (2, 'test2'), (1, 'test3'), (1, 'test4')

CREATE TABLE table2
(
num INT,
concatenation VARCHAR(1000)
)

INSERT INTO table2
SELECT num,
Substring((SELECT DISTINCT ',' + T2.val
FROM table1 T2
WHERE T1.num = T2.num
FOR XML PATH('')), 2, 1000000)
FROM table1 T1
GROUP BY num


Now I updated the first record using the query

UPDATE table2
SET concatenation = 'test1,test25,test4'
WHERE num = 1


Now insert few more records in the
table1


INSERT INTO table1(num,val)
VALUES (3,'test5'),(1,'test6')(1,'test1')


Now I want to update the
table2
records with updated rows without duplicate. Here I want to concatenate if text is not there and avoid if a duplicate is there.

Expected results: my update of table2 result should get the expected result as follow

select * from table2


Output:

Num concatenation
-----------------------------------
1 test1,test25,test3,test4,test6
2 test2
3 test5

Answer

You can as the blow:

;WITH SplitTable2
AS
(
    SELECT 
        A.*,
        O.splitdata 
    FROM
     (SELECT *,  cast('<X>'+replace(F.concatenation,',','</X><X>')+'</X>' as XML) as xmlfilter from @table2 F) A
     CROSS APPLY ( SELECT fdata.D.value('.','varchar(50)') as splitdata FROM A.xmlfilter.nodes('X') as fdata(D)) O 
), ResultList
AS
(
    SELECT S.num, S.splitdata AS val FROM SplitTable2 S
    UNION
    SELECT T.num, T.val FROM @table1 T
), Result
AS
(
    SELECT DISTINCT
        R.num,      
        STUFF((SELECT ',' + T2.val FROM ResultList T2 WHERE T2.num = R.num FOR XML PATH('')), 1, 1, '') val
    FROM
        ResultList R
)

SELECT * FROM Result

Result:

num         val                               
----------- ----------------------------------
1           test1,test25,test3,test4,test6
2           test2
3           test5

Answer of COMMENT

;WITH PrepareVal
AS
(
    SELECT 
        T1.num ,
        T1.val
    FROM
       @table1 T1
    WHERE
        NOT EXISTS
        (
            SELECT 1 FROM @table2 T2
            WHERE
                T2.num = T1.num AND 
                ',' +  T2.concatenation + ',' LIKE '%,' + T1.val + ',%'
        )

), ResultList
AS
(
    SELECT DISTINCT
        R.num,      
        STUFF((SELECT ',' + T2.val FROM PrepareVal T2 WHERE T2.num = R.num FOR XML PATH('')), 1, 1, '') val
    FROM
        PrepareVal R
), Result
AS
(
    SELECT 
        T2.num ,
        T2.concatenation + CASE WHEN R.val IS NOT NULL THEN ',' + R.val ELSE '' END AS concatenation    
    FROM    
        @table2 T2 LEFT JOIN 
        ResultList R ON T2.num = R.num

    UNION ALL

    SELECT 
        R.num ,
         R.val AS concatenation     
    FROM            
        ResultList R LEFT JOIN 
        @table2 T2 ON T2.num = R.num
    WHERE
        T2.num IS NULL 
)

SELECT * FROM Result