Saturday, May 29, 2021

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


No comments:

Post a Comment