sql server using recrusive cte to get the level in the same group

Question

I have a sql server table showing the IDs and their previous IDs,

create table test2 ( ID varchar(10) ,
                     Pre_ID varchar(10)
)

insert into test2 values ('e','d')
                          , ('d','c')
                          , ('c','b')
                          , ('b','a')
                          , ('a',null)
                          , ('r','q')
                          , ('q','p')
                          , ('p',null)

the table is like this:

The result should be like this:

I have successfully got the levels using a recursive cte, but could not get the correct group for them. can anyone help? Thanks.

This is my code:

with cte as (
select id, Pre_ID, level
from #temp2
where Pre_ID is null

union all 

select t2.id, t2.Pre_ID, t2.level
from cte 
inner join #temp2 t2
on t2.Pre_ID=cte.id
)
select * from cte
order by id

Answer 1

What you need to do is start with the first level and add a ROW_NUMBER to that, then join all further levels recursively:

with cte as (
    select id, Pre_ID, level, row_number() over (order by ID) as grp
    from #temp2
    where Pre_ID is null

    union all 

    select t2.id, t2.Pre_ID, t2.level, cte.grp
    from cte 
    inner join #temp2 t2
        on t2.Pre_ID=cte.id
)
select * from cte
order by id;