Example
Table Setup
CREATE TABLE dbo.Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)
GO
INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employees VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employees VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employees VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employees VALUES (105, 'Žydrė', 'Klybė', 101)
INSERT INTO Employees VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employees VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employees VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employees VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employees VALUES (110, 'Michael', 'Kensington', 106)
Common Table Expression
;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
FROM Employees AS e
INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees 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 |
| Žydre Klybe | 2 | Ken Sánchez |
| Joseph Walker | 3 | Keith Hall |
| Peter Miller | 3 | Fred Bloggs |
| Sam Jackson | 3 | Žydre Klybe |
| Chloe Samuels | 3 | Žydre Klybe |
| George Weasley | 3 | Žydre Klybe |
| Michael Kensington | 4 | Sam Jackson |
