Douglas Gaskell Douglas Gaskell - 1 month ago 14
SQL Question

Associate employees to departments based on hierarchy tree

I am trying to figure out how to build a query to sort out department associations based on the organizational hierarchy. So if

John
is in
sales
and reports to
Jane
who reports to
Mike
.
Jane
should inherit
sales
, and
Mike
should also inherit the sales department.

Example Table:

+-------+------------+------------+
| Name | Department | Supervisor |
+-------+------------+------------+
| John | Sales | Jane |
| Jane | Supervisor | Mike |
| Fred | Supervisor | Mike |
| Alex | Retail | Fred |
| Mike | Manager | --- |
| Chris | Sales | Jane |
| Dan | Retail | Fred |
+-------+------------+------------+


What the tree would look like:

enter image description here

Expected Output:

+-------+------------+
| Name | Department |
+-------+------------+
| John | Sales |
| Jane | Supervisor |
| Jane | Sales |
| Fred | Supervisor |
| Fred | Retail |
| Alex | Retail |
| Mike | Manager |
| Mike | Sales |
| Mike | Retail |
| Chris | Sales |
| Dan | Retail |
+-------+------------+


I've tried a number of things over the last 2 weeks, but have not even got close to representing the output I desire. I believe that this is going beyond my SQL knowledge, and I have no (even partially) working code to show here.

How can I achieve this?

CREATE TABLE Employees
(Name varchar(5), Department varchar(10), Supervisor varchar(4))
;

INSERT INTO Employees
(Name, Department,Supervisor)
VALUES
('John', 'Sales', 'Jane'),
('Jane', 'Supervisor', 'Mike'),
('Fred', 'Supervisor', 'Mike'),
('Alex', 'Retail', 'Fred'),
('Mike', 'Manager', '---'),
('Chris', 'Sales', 'Jane'),
('Dan', 'Retail', 'Fred')
;

Answer Source

You can do this with CTE using recursion.

Link on MSDN to know more about recursive CTE

Query below

--CREATE TABLE Employees
--    (Name varchar(5), Department varchar(10), Supervisor varchar(4))
--;

--INSERT INTO Employees
--    (Name, Department, Supervisor)
--VALUES
--    ('John', 'Sales', 'Jane'),
--    ('Jane', 'Supervisor', 'Mike'),
--    ('Fred', 'Supervisor', 'Mike'),
--    ('Alex', 'Retail', 'Fred'),
--    ('Mike', 'Manager', '---'),
--    ('Chris', 'Sales', 'Jane'),
--    ('Dan', 'Retail', 'Fred')
--;

WITH user_cte AS (
SELECT Name, Department,Supervisor
FROM Employees
WHERE Department ='Retail' or Department='Sales'
UNION ALL
SELECT 
t.Name, ucte.Department,t.Supervisor
FROM Employees t
INNER JOIN user_cte ucte ON ucte.Supervisor = t.Name
)
SELECT DISTINCT Name,Department
FROM user_cte
UNION 
SELECT Name, Department 
FROM Employees
GO

Explanation:

Recursive CTEs are a common programming construct in SQL server. Important thing to note is that by default they nest up to 100 levels. If you need to nest deeper use MAXRECURSION query hint

CTE alone does not give you the complete output and you need to add up the missing rows from original table using UNION expression.