Employee - Manager hierarchy

[ Just Copy & Paste ]

declare @TBLEmployee table(EmpID int,EmpName varchar(10),MgrID int null)

insert into @TBLEmployee values(101,'Boss',null)
insert into @TBLEmployee values(1,'A',101)
insert into @TBLEmployee values(2,'B',3)
insert into @TBLEmployee values(3,'C',101)
insert into @TBLEmployee values(4,'D',2)

;WITH DirectReports (EmpID,EmpName, MgrID, MgrName)
AS
(
-- Anchor member definition
SELECT e.EmpID, e.EmpName, e.MgrID,null--,0 AS Level
FROM @TBLEmployee AS e
WHERE e.MgrID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.EmpID, e.EmpName, e.MgrID,eM.EmpName--,Level + 1
FROM @TBLEmployee AS e
INNER JOIN @TBLEmployee AS eM
ON e.MgrID = eM.EmpID
)
select * from DirectReports