Get Hierarchy structure for Employee/Manager

PHOTO EMBED

Thu Feb 16 2023 13:54:17 GMT+0000 (Coordinated Universal Time)

Saved by @TEST12 #sql 

CREATE TABLE Employee
(
	ID				INT,
	FirstName		VARCHAR(50),
	LastName		VARCHAR(50),
	DepartmentID	INT,
	ReportToID		INT
)
CREATE Table Departments
(
	ID				INT,
	DepartmentName	VARCHAR(50)
)
INSERT INTO Employee VALUES (1, 'F1', 'L1', 1, NULL);
INSERT INTO Employee VALUES (2, 'F2', 'L2', 2, 1);
INSERT INTO Employee VALUES (3, 'F3', 'L3', 1, 2);
INSERT INTO Employee VALUES (4, 'F4', 'L4', 2, 3);
INSERT INTO Employee VALUES (5, 'F5', 'L5', 1, NULL);
INSERT INTO Employee VALUES (6, 'F6', 'L6', 2, 1);
INSERT INTO Employee VALUES (7, 'F7', 'L7', 1, 2);
INSERT INTO Departments VALUES ( 1 , 'D1');
INSERT INTO Departments VALUES ( 2 , 'D2');
INSERT INTO Departments VALUES ( 3 , 'D3');
SELECT *
FROM Employee
SELECT *
FROM Departments
;WITH cte AS
(
SELECT ID,firstname,lastname,departmentid,reporttoid
FROM Employee WHERE FirstName='F2' -- Change this
UNION ALL
SELECT a.ID,a.firstname,a.lastname,a.departmentid,a.reporttoid
FROM Employee a
INNER JOIN cte b ON a.ReportToID=b.Id
),cte1 AS 
(
SELECT CTE.iD AS EmployeeId
	,e.Id AS ManagerId
	,cte.FirstName AS EmployeeFirstName
	,cte.LastName AS EmployeeLastName
	,e.FirstName AS ManagerFirstName
	,e.LastName AS ManagerLastName
	,cte.DepartmentId  AS EmpDepId--Emp
	,e.DepartmentId AS MgrDepId-- Mgr
FROM cte
	CROSS APPLY Employee e
WHERE cte.Reporttoid=e.ID
),cte2 AS 
(
SELECT cte.*
	,d1.DepartmentName AS EmployeeDepartmentName
FROM cte1 AS cte
	LEFT JOIN Departments AS d1
		ON cte.EmpDepId=d1.ID
)
SELECT cte2.EmployeeId
	,cte2.ManagerId
	,cte2.EmployeeFirstName
	,cte2.EmployeeLastName
	,cte2.ManagerFirstName
	,cte2.ManagerLastName
	,cte2.EmployeeDepartmentName
	,d2.DepartmentName AS ManagerDepartmentName
FROM cte2		
	LEFT JOIN Departments AS d2
		ON cte2.MgrDepId=d2.Id
content_copyCOPY

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/11a575eb-67c6-436d-bd2c-f5da8ca64e21/get-hierarchy-structure-for-employeemanager?forum