-- Create sample table with employees and managers
CREATE TABLE Employees(
employee_nbr INT NOT NULL PRIMARY KEY,
employee_name VARCHAR(35),
manager_nbr INT NULL REFERENCES Employees(employee_nbr));
INSERT INTO Employees VALUES (1, 'John Doe', NULL);
INSERT INTO Employees VALUES (2, 'James Brown', 1);
INSERT INTO Employees VALUES (3, 'Keith Green', NULL);
INSERT INTO Employees VALUES (4, 'Peter Roth', 2);
INSERT INTO Employees VALUES (5, 'Hans Gruber', 2);
INSERT INTO Employees VALUES (6, 'Kris Evans', 4);
INSERT INTO Employees VALUES (7, 'Jeff Colleman', NULL);
-- Use recursive CTE to build binary and charachter paths
WITH EmployeeHierarchy
AS
(SELECT employee_nbr, employee_name, manager_nbr,
CAST(employee_nbr AS VARBINARY(MAX)) AS bpath,
CAST('.' +
CAST(employee_nbr AS VARCHAR(4)) +
'.' AS VARCHAR(MAX)) AS cpath
FROM Employees
WHERE manager_nbr IS NULL
UNION ALL
SELECT E.employee_nbr, E.employee_name, E.manager_nbr,
H.bpath + CAST(E.employee_nbr AS BINARY(4)),
H.cpath + CAST(E.employee_nbr AS VARCHAR(4)) + '.'
FROM Employees AS E
JOIN EmployeeHierarchy AS H
ON E.manager_nbr = H.employee_nbr)
SELECT employee_nbr, employee_name, manager_nbr, bpath, cpath
FROM EmployeeHierarchy
WHERE cpath LIKE '%.2.%' -- filter all employees for manager 2
ORDER BY bpath; -- order by natural hierarchy path
-- Results
employee_nbr employee_name manager_nbr bpath cpath
------------ -------------- ----------- ---------------------------------- ----------
2 James Brown 1 0x0000000100000002 .1.2.
4 Peter Roth 2 0x000000010000000200000004 .1.2.4.
6 Kris Evans 4 0x00000001000000020000000400000006 .1.2.4.6.
5 Hans Gruber 2 0x000000010000000200000005 .1.2.5.
Labels: adjacency list, common table expressions, hierarchy, t-sql programming, trees