FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Employee (EmployeeID INT, ManagerID INT)

INSERT INTO Employee (EmployeeID, ManagerID)
VALUES
  (1, NULL),
  (2, 1),
  (3, 1),
  (4, 2),
  (5, 2),
  (6, 1),
  (7, 6),
  (8, 6),
  (9, NULL),
  (10, 3),
  (11, 3),
  (12, 10),
  (13, 9),
  (14, 9),
  (15, 13)

;WITH RecursiveCTE AS
(
    SELECT
        EmployeeID = E.EmployeeID,
        ManagerID = NULL, -- Always null by WHERE filter
        HierarchyLevel = 1,
        HierarchyRoute = CONVERT(VARCHAR(MAX), E.EmployeeID)
    FROM
        Employee AS E
    WHERE
        E.ManagerID IS NULL

UNION ALL

SELECT
        EmployeeID = E.EmployeeID,
        ManagerID = E.ManagerID,
        HierarchyLevel = R.HierarchyLevel + 1,
        HierarchyRoute = R.HierarchyRoute + ' -> ' + CONVERT(VARCHAR(10), E.EmployeeID)
    FROM
        RecursiveCTE AS R
        INNER JOIN Employee AS E ON R.EmployeeID = E.ManagerID
)
SELECT
    R.HierarchyLevel,
    R.EmployeeID,
    R.ManagerID,
    R.HierarchyRoute
FROM
    RecursiveCTE AS R
ORDER BY
    R.HierarchyLevel,
    R.EmployeeID

HierarchyLevel	EmployeeID	ManagerID	HierarchyRoute
1	1	NULL	1
1	9	NULL	9
2	2	1	1 -> 2
2	3	1	1 -> 3
2	6	1	1 -> 6
2	13	9	9 -> 13
2	14	9	9 -> 14
3	4	2	1 -> 2 -> 4
3	5	2	1 -> 2 -> 5
3	7	6	1 -> 6 -> 7
3	8	6	1 -> 6 -> 8
3	10	3	1 -> 3 -> 10
3	11	3	1 -> 3 -> 11
3	15	13	9 -> 13 -> 15
4	12	10	1 -> 3 -> 10 -> 12

(15 rows affected)

(15 rows affected)