CODEWITHSUNDEEP
sqlsql-server
Douglas Gaskell
Douglas Gaskell

Reputation: 10030

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')
;

Upvotes: 2

Views: 303

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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.

Upvotes: 2

Related Questions