aks aks - 7 months ago 13
SQL Question

Need help to find output from a parent child combination

Sample records:

DECLARE @Temp TABLE (
ID INT,
Child VARCHAR(5),
Parent VARCHAR(5),
Comment VARCHAR(5)
)
INSERT @Temp VALUES
(1, 'C1', 'P1', 'AA'),
(1, 'C2', 'P2', '' ),
(1, 'C3', 'P3', 'XX'),
(1, 'C4', 'P4', 'YY'),
(1, 'C5', 'P5', '' ),
(1, 'C6', 'P6', 'ZZ'),
(1, 'C7', 'P7', '' ),
(1, 'C8', 'P8', '' ),
(1, 'C9', 'P9', 'TT'),
(1, 'C10','P10','')


The records of source table will look like records of the above @temp table.
Can we design a TSQL script which can use @temp as a source table and that script can give the output like the below result table.

IF the comment value is NULL/empty for a parent child combination, then the next succeeding NOT NULL/non empty comment need to replace.




The Output what I need is like this

ID Child Parent [Comment]
1 C1 P1 AA
1 C2 P3 XX
1 C3 P3 XX
1 C4 P4 YY
1 C5 P6 ZZ
1 C6 P6 ZZ
1 C7 P9 TT
1 C8 P9 TT
1 C9 P9 TT
1 C10 P10


Any kind of help would be highly appreciated!!
Thanks

Answer

With the help of recursive cte:

;WITH cte AS (
SELECT ID, Child, Parent, Comment
FROM @Temp
UNION ALL
SELECT t.ID, t.Child, c.Parent, c.Comment
FROM cte c
INNER JOIN @Temp t 
    ON CAST(STUFF(c.Child,1,1,'') as int) = CAST(STUFF(t.Child,1,1,'') as int)+1
WHERE t.comment = ''
)

SELECT  MAX(ID) as ID,
        Child,
        MAX(Parent) as Parent,
        MAX(Comment) as Comment
FROM cte
GROUP BY Child
ORDER BY CAST(STUFF(Child,1,1,'') as int)

Output:

ID          Child Parent Comment
----------- ----- ------ -------
1           C1    P1     AA
1           C2    P3     XX
1           C3    P3     XX
1           C4    P4     YY
1           C5    P6     ZZ
1           C6    P6     ZZ
1           C7    P9     TT
1           C8    P9     TT
1           C9    P9     TT
1           C10   P10    

(10 row(s) affected)