Get Team Members of A Supervisor

/*

      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