Create Employee and Manager Hierarchy using Common Table Expression

We will see how to get the Employee-Manager hierarchy from the same table.

Let's setup the table first.

CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)

insert values into the table

INSERT INTO Employee VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employee VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employee VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employee VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employee VALUES (105, 'Zydr', 'Klyb', 101)
INSERT INTO Employee VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employee VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employee VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employee VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employee VALUES (110, 'Michael', 'Kensington', 106)

See the values below.

EmployeeID	FirstName	LastName	ManagerID
101	Ken	Sánchez	NULL
102	Keith	Hall	101
103	Fred	Bloggs	101
104	Joseph	Walker	102
105	Zydr	Klyb	101
106	Sam	Jackson	105
107	Peter	Miller	103
108	Chloe	Samuels	105
109	George	Weasley	105
110	Michael	Kensington	106

Below SQL Code to demonstrate the Employee and Manager relationship.

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM Employee AS e
	INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employee WHERE EmployeeID = cteReports.SupervisorID)
AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Output:

FullName	EmpLevel	ManagerName
Ken Sánchez	1	NULL
Keith Hall	2	Ken Sánchez
Fred Bloggs	2	Ken Sánchez
Zydr Klyb	2	Ken Sánchez
Joseph Walker	3	Keith Hall
Peter Miller	3	Fred Bloggs
Sam Jackson	3	Zydr Klyb
Chloe Samuels	3	Zydr Klyb
George Weasley	3	Zydr Klyb
Michael Kensington	4	Sam Jackson