Hierarchy of Employee with CTE

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, reportees
where #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