SQL query to get organization hierarchy in SQL Server
To get the best out of this blog, the following concepts need to be understood first.
1. Self-Join
2. CTE
3. Recursive CTE
Here is the problem definition:
1. Employees table contains the following columns
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
SQL query to get organization hierarchy
For example,
Scenario 1: If we pass David’s EmployeeId to the query, then it should display the organization hierarchy as shown below.
organization hierarchy sql server
Scenario 2: If we pass Lara’s EmployeeId to the query, then it should display the organization hierarchy as shown below.
organizational hierarchy sql
We will be using the following Employees table for this demo
sql server query to display org chart
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