Select starting node ID in CTE 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
You add the column, in both parts of the recursive CTE.

Starting nodes have the ID as startingNode id.

The descendants have as starting node id, the starting node id of their parent:

WITH ret AS
  (
    SELECT id AS idStartingNode, o.*
    FROM    osoba o
    --WHERE   ID = 1
    UNION ALL
    SELECT r.idStartingNode, o.*
    FROM    osoba o INNER JOIN
            ret r ON o.idNd = r.ID
  ) ;