How to read all records recursively and show by level depth TSQL

recursion, select, t-sql

Solution

;with C as
(
  select id,
         parent,
         value,
         0 as depth
  from YourTable
  where id = 3
  union all
  select T.id,
         T.parent,
         T.value,
         C.depth + 1
  from YourTable as T
    inner join C  
      on T.parent = C.id
)
select *
from C

SE-Data

Problem

Is there a way to read records recursively in similar table and order by depth level? ``` #table: id int | parent int | value string -------------------------------------------- 1 -1 some 2 1 some2 3 2 some3 4 2 some4 5 3 some5 6 4 some6 7 3 some5 8 3 some5 9 8 some5 10 8 some5 ``` So is there a way to recursively select where result table would look like this. ``` select * from #table where id=3 id int | parent int | value string | depth -------------------------------------------------------- 3 2 some3 0 5 3 some5 1 7 3 some5 1 8 3 some5 1 9 8 some5 2 10 8 some5 2 ``` So if I choose id=3 I would see recursion for id=3 and children Thank you

Original source