How to find root-nodes

Question

id  Parant_ID       sort_nm    Scheme_Name
5   5               CAMPA      CAMPA
6   5               NPV        Net Present Value
7   5               CA         Compensatory Afforestation
8   6               ACA        Additional Compensatory  Afforestation
43  8               asd        asdasd
45  45              new        new
46  45              asdaasdas  asdasdasdas

I have the above tree structure in SQL Server.
I want to know the root node id of every node.

Answer 1

You can use a recursive CTE. Start from the root and carry the RootID through the recursion.

with C as
(
  select id,
         Parant_ID,
         sort_nm,
         Scheme_Name,
         id as RootID
  from YourTable
  where id = Parant_ID
  union all
  select T.id,
         T.Parant_ID,
         T.sort_nm,
         T.Scheme_Name,
         C.RootID
  from YourTable as T
    inner join C
      on T.Parant_ID = C.id
  where T.id <> T.Parant_ID
)
select *
from C

SE-Data