Hierarchy of Employees in Sql Server with CTE

In this post, I am sharing the solution to find the hierarchy of employees using recursive CTE.

The solution will list all the employees coming under the hierarchy of an employee.

create table #tblemployee  (
      empid   varchar(10) primary key
    , empname varchar(10)
    , mgrid   varchar(10)
  )

insert into #tblemployee
select 'Emp001', 'Satish', NULL union all
select 'Emp002', 'Amit', 'Emp001'  union all
select 'Emp003', 'Sumit', 'Emp001' union all
select 'Emp004', 'Anil', 'Emp002' union all
select 'Emp005', 'Tarun', 'Emp003' union all
select 'Emp006', 'Sandeep', 'Emp003' union all
select 'Emp007', 'Abhay', 'Emp006' union all
select 'Emp008', 'Deepak', 'Emp002' union all
select 'Emp009', 'Suman', 'Emp007' union all
select 'Emp010', 'Raman', 'Emp007'

;with reportees as (
    select empid, empname, mgrid, 1 as level
    from   #tblemployee    
    where  mgrid = 'Emp001'
    union all
    select #tblemployee.empid, #tblemployee.empname, #tblemployee.mgrid, level + 1
    from   #tblemployee

    inner join reportees on #tblemployee.mgrid = reportees.empid
)

select  a.empid, a.empid, a.mgrid, b.empname as mgrname, level
from    reportees a
left join #tblemployee b on a.mgrid = b.empid
order by level

drop table #tblemployee

OUTPUT