-- Create test table Temp_Employees
CREATE TABLE [dbo].[Temp_Employees](
[EmpId] [int] NULL,
[Name] [varchar](100) NULL,
[DeptId] [int] NULL,
[MgrId] [int] NULL
)
-- Insert some dummy Records in test table Temp_Employees
INSERT INTO Temp_Employees (EmpId,Name ,DeptId,MgrId ) Values
(1,'John', 1, NULL ),
(2,'Dinesh', 1, 1),
(3,'Rohan', 1, 1),
(4,'Babu', 1, 2),
(5,'Sandy', 1, 2),
(6,'Allan', 1, 5),
(7,'Duminy', 1, 5),
(8,'Ram', 1, 4),
(9,'Mark', 3, 2),
(10,'Dough', 6, 1)
-- select/check inserted records in table Temp_Employees
select * FROM Temp_Employees
-- write recursive CTE to show Employee, manager and level of Employee in organization
; WITH RecursiveCTE (Empid,EmpName ,Manager,LevelInOrganization)
AS(
SELECT Empid ,Name as EmpName, cast(' ' as varchar) AS Manager,0 FROM Temp_Employees where MgrId IS NULL
UNION ALL
SELECT e.Empid ,e.Name as EmpName ,CAST(Rc.EmpName as varchar) AS Manager,LevelInOrganization + 1
FROM Temp_Employees E
INNER JOIN RecursiveCTE Rc on e.MgrId = rc.Empid
)
select * FROM RecursiveCTE
Drop table Temp_Employees
Note: Please check/execute above queries in development/test environment only
