/*
Suppose you have table Employees as shown below
EmployeeID FirstName LastName ReportsTo
1 Nancy Davolio 2
2 Andrew Fuller NULL
3 Janet Leverling 2
4 Margaret Peacock 2
5 Steven Buchanan 2
6 Michael Suyama 5
7 Robert King 5
8 Laura Callahan 2
9 Anne Dodsworth 5
and you want to find employes to whom Steven Buchanan supervise
you can use below query to retrieve the required list
*/
--Get Team Members of A Supervisor
Declare @EmployeeID Int
Set @EmployeeID = 5
;WITH GetEmployee(EmployeeId,EmpName)
AS (
Select EmployeeId,FirstName + ' ' + LastName as EmpName
From Employees E
Where EmployeeId=@EmployeeID
UNION ALL
Select E.EmployeeId,FirstName + ' ' + LastName as EmpName
From Employees E,GetEmployee G
Where E.ReportsTo =G.EmployeeId
)
Select *
From GetEmployee
