Solution to TSQL Beginners Challenge 17

;with cte01 as (--split xml into table		
    SELECT EmpId=R2.value('data(./@EmpId)', 'int')
          ,EmpName=R2.value('data(./@EmpName)', 'varchar(max)')
          ,ReportsTo=R2.value('data(./@ReportsTo)', 'varchar(max)')
    FROM @Employees  cross apply EmpRecord.nodes('//Employee') as B(R2)
)
, cte02 as (
  select EmpId, EmpName, ReportsTo, Stack=Empname,Lev=1
  from cte01 where EmpId=@EmpId
 
  union all
  select C1.EmpId, C1.EmpName, C1.ReportsTo, C2.Stack+'/'+C1.EmpName, Lev=C2.Lev+1
  from cte02 C2 join cte01 C1 on C1.EmpId=C2.ReportsTo
  
  
)
, cte03 as (
select EmpId
       ,Empname 
       ,ReportsTo
       ,Stack
       ,rn=row_number() over (order by Lev desc)-1
from cte02
)
select Level=rn
       ,Path=Stack
       ,Hierarchy=replicate(' ', rn)+Empname 
from cte03 order by rn      

Tags: