Hierarchical query to return all children with root parent

In SQL Server, I have a table with sample data as shown below:

SELECT parent_id,child_id FROM tab a

parent_id child_id
--------------------
1 2
2 3
3 4
1 5
5 6
12 13


And I want to fetch data with parent_id for all children like this:

Parent_id Child_id
--------------------
1 2
1 3
1 4
1 5
1 6
12 13


Where first parent is shown for all relevant children. I have tried below,

WITH cte_Recursive( Parent, Child, Level ) AS
(
SELECT T.Parent_id, T.Child_id, 1 AS Level
FROM tab AS T
WHERE NOT EXISTS(SELECT * FROM tab AS TI
WHERE T.Child_id = TI.Parent_id)

UNION ALL

SELECT TR.Parent_id, TR.Child_id, Level + 1
FROM tab AS TR
INNER JOIN cte_Recursive CR ON TR.Child_id = CR.Parent
)
SELECT
Parent, Child
FROM
(SELECT
CR.Parent, CR.Level, iTVF.Child,
max_level = MAX(CR.Level) OVER (PARTITION BY NULL)
FROM
cte_Recursive CR
CROSS APPLY
(SELECT
CRI.Parent, CRI.Child
FROM
cte_Recursive CRI
WHERE
CR.Level >= CRI.Level) iTVF
) AS S
WHERE
Level = max_level


But it does not show the expected result

1 Answer
1

A query like below will help
See working demo here

; with cte as
(
select t1.parent_id, t1.child_id
from tab t1
left join tab t2 on t1.parent_id = t2.child_id
where t2.parent_id is null

union all

select c.parent_id, t2.child_id
from cte c
join tab t2 on t2.parent_id = c.child_id
)
select *
from cte
order by child_id


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.