Part 2 - SQL query to get organization hierarchy

Suggested :
Part 1 - How to find nth highest salary in sql


1. Employees table contains the following columns Here is the problem definition:
    a) EmployeeId, 
    b) EmployeeName 
    c) ManagerId 
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.

Consider the following organization hierarchy chart


For example, 
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy as shown below.


Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy as shown below.


We will be using the following Employees table for this demo


SQL to create and populate Employees table with test data

Create table Employees

(

     EmployeeID int primary key identity,

     EmployeeName nvarchar(50),

     ManagerID int foreign key references Employees(EmployeeID)

)

GO

Insert into Employees values ('John', NULL)

Insert into Employees values ('Mark', NULL)

Insert into Employees values ('Steve', NULL)

Insert into Employees values ('Tom', NULL)

Insert into Employees values ('Lara', NULL)

Insert into Employees values ('Simon', NULL)

Insert into Employees values ('David', NULL)

Insert into Employees values ('Ben', NULL)

Insert into Employees values ('Stacy', NULL)

Insert into Employees values ('Sam', NULL)

GO

Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')

Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')

Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')

Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')

Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')

GO

Here is the SQL that does the job

Declare @ID int ;

Set @ID = 7;

WITH EmployeeCTE AS

(

     Select EmployeeId, EmployeeName, ManagerID

     From Employees

     Where EmployeeId = @ID

    

     UNION ALL

    

     Select Employees.EmployeeId , Employees.EmployeeName,

             Employees.ManagerID

     From Employees

     JOIN EmployeeCTE

     ON Employees.EmployeeId = EmployeeCTE.ManagerID

)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName

From EmployeeCTE E1

LEFT Join EmployeeCTE E2

ON E1.ManagerID = E2.EmployeeId