I have following table:
create table osoba(
id int primary key,
idNd int,
nazwim nvarchar(500),
)
Data looks like below:
id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 grandchild1child1 5 2 grandchild2child1 6 3 grandchild1child2 7 3 grandchild2child2
I've got following cte:
;WITH ret AS(
SELECT *
FROM osoba
--WHERE ID = 1
UNION ALL
SELECT o.*
FROM osoba o INNER JOIN
ret r ON o.idNd = r.ID)
Select * from ret returns all descendants for each node and starting node:
id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 grandchild1child1 5 2 grandchild2child1 6 3 grandchild1child2 7 3 grandchild2child2 6 3 grandchild1child2 7 3 grandchild2child2 4 2 grandchild1child1 5 2 grandchild2child1 2 1 child1 3 1 child2 6 3 grandchild1child2 7 3 grandchild2child2 4 2 grandchild1child1 5 2 grandchild2child1
How to retrieve information about starting node for each record? I would like to get result like that:
idStartingNode id idNd nazwim 1 1 NULL grandpa 1 2 1 child1 1 3 1 child2 1 4 2 grandchild1child1 1 5 2 grandchild2child1 1 6 3 grandchild1child2 1 7 3 grandchild2child2 3 6 3 grandchild1child2 3 7 3 grandchild2child2 2 4 2 grandchild1child1 2 5 2 grandchild2child1 2 2 1 child1 3 3 1 child2 6 6 3 grandchild1child2 7 7 3 grandchild2child2 4 4 2 grandchild1child1 5 5 2 grandchild2child1