SQL – Recursive select parent child

You’ll need your recursion to also build something that can be sorted by at the end:

declare @t TABLE (
[CHILD] [int] NOT NULL,
[PARENT] [int] NOT NULL
) 

insert @t values
( 0, -1),   -- I added this
( 1, 2 ),
( 2, 0 ),
( 3, 1 ),
( 4, 2 ),
( 5, 0 )

(note I have added a true root element)

;with n(CHILD, PARENT, GENERATION, hierarchy) as (
select CHILD, PARENT,0, CAST(CHILD as nvarchar) as GENERATION from @t
where PARENT=-1
union all
select nplus1.CHILD, nplus1.PARENT, GENERATION+1,
cast(n.hierarchy + '.' + CAST(nplus1.child as nvarchar) as nvarchar)
 from
@t as nplus1 inner join n on nplus1.PARENT=n.CHILD
)
select CHILD,GENERATION
from n
order by hierarchy

returns

CHILD GENERATION
0           0
2           1
1           2
3          3
4          2
5          1