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 |
No comments:
Post a Comment