SQL Breakdown Hierarchy

Question

Starting with a basic Employee/Supervisor Hierarchy, I am using recursive CTE to build out the levels:

WITH EmployeeSupervisor
AS (
    SELECT *
    FROM (
        VALUES ('mike','lisa')
            ,('kevin','lisa')
            ,('lisa','ken')
            ,('ken','scott')
            ,('scott','chris')
            ,('chris','')
        ) RawData(emp, sup)
    )
    ,Hier
AS (
    -- anchor level, no supervisor
    SELECT 1 AS lvl
        ,emp
        ,sup
    FROM EmployeeSupervisor
    WHERE sup = ''
    
    UNION ALL
    
    -- recursive member
    SELECT H.lvl + 1 AS lvl
        ,ES.emp
        ,ES.sup
    FROM EmployeeSupervisor ES
    INNER JOIN Hier H
        ON ES.sup = H.emp
    WHERE H.lvl + 1 <= 5 -- max of 5 levels
        AND ES.sup != ''
    )
SELECT *
FROM Hier

I have tried a few variations on PIVOT with COALESCE to get the desired output (shown by query below), with no success.

-- expected output
SELECT *
    FROM (
        VALUES ('mike','lisa','ken','scott','chris')
            ,('kevin','lisa','ken','scott','chris')
            ,('lisa','ken','scott','chris', NULL)
            ,('ken','scott','chris', NULL, NULL)
            ,('scott','chris', NULL, NULL, NULL)
            ,('chris',NULL, NULL, NULL, NULL)
        ) Expected(lvl1, lvl2,lvl3,lvl4,lvl5)

There are lots of similar questions out there, but none that truly addresses the nature of this one.

Edit: Using SQL Server 2016, and hoping to avoid numerous, repeated joins in favor or recursive CTE.

Which engine are you using? – Bart Schuijt Aug 10 '20 at 18:58 — Bart Schuijt, Aug 10 '20 at 18:58
@BartSchuijt SQL Server 2016 – Michael K Aug 10 '20 at 19:00 — Michael K, Aug 10 '20 at 19:00

marcothesane · Answer 1 · 2020-08-10T23:51:54.163

Actually, you can look at it differently. You want 5 levels side-by-side, hard-wiring it to 5 levels. You might just as well build it as a hard-wired 5-way left self join ...

WITH
rawdata(ord,emp, sup) AS (  -- adding an order integer, to keep the order
          SELECT 1,'mike','lisa'
UNION ALL SELECT 2,'kevin','lisa'
UNION ALL SELECT 3,'lisa','ken'
UNION ALL SELECT 4,'ken','scott'
UNION ALL SELECT 5,'scott','chris'
UNION ALL SELECT 6,'chris',''
)
SELECT
  l0.emp
, l1.emp
, l2.emp
, l3.emp
, l4.emp
FROM      rawdata l0
LEFT JOIN rawdata l1 ON l0.sup=l1.emp
LEFT JOIN rawdata l2 ON l1.sup=l2.emp
LEFT JOIN rawdata l3 ON l2.sup=l3.emp
LEFT JOIN rawdata l4 ON l3.sup=l4.emp
ORDER BY l0.ord
;
-- out   emp  |  emp   |  emp   |  emp   |  emp  
-- out -------+--------+--------+--------+-------
-- out  mike  | lisa   | ken    | scott  | chris
-- out  kevin | lisa   | ken    | scott  | chris
-- out  lisa  | ken    | scott  | chris  | (null)
-- out  ken   | scott  | chris  | (null) | (null)
-- out  scott | chris  | (null) | (null) | (null)
-- out  chris | (null) | (null) | (null) | (null)

A next attempt concatenates a path string and then uses the SQL Server function TOKEN() to split the path into columns ...

WITH RECURSIVE r AS (
  SELECT 
    1 AS lvl
  , emp AS path
  , *
  FROM rawdata
  WHERE sup=''
  UNION ALL
  SELECT
    p.lvl + 1
  , p.path + ',' + c.emp AS path
  , c.*
  FROM rawdata c
  JOIN r AS p
  ON c.sup = p.emp
)
SELECT
  TOKEN(path,',',1) AS s1
, TOKEN(path,',',2) AS s2
, TOKEN(path,',',3) AS s3
, TOKEN(path,',',4) AS s4
, TOKEN(path,',',5) AS s5
FROM r;
--   s1   |  s2   | s3  |  s4  |  s5   
-- -------+-------+-----+------+-------
--  chris |       |     |      | 
--  chris | scott |     |      | 
--  chris | scott | ken |      | 
--  chris | scott | ken | lisa | 
--  chris | scott | ken | lisa | mike
--  chris | scott | ken | lisa | kevin

Thanks for your response. I agree with this approach, but the original problem is a nested hierarchy with up to 20 levels (this is a simple example). I understand I could simply add another 15 joins, but I am hoping for a solution that uses the recursive CTE. I'll accept this as the answer if no other solutions are offered using the recursive CTE — Michael K, Aug 10 '20 at 18:40

kkica · Answer 2 · 2020-08-10T19:46:44.857

You can hold all the previous superiors and create a hierarchy in a csv field. (check the third field, that I added) So

  WITH EmployeeSupervisor
AS (
    SELECT *
    FROM (
        VALUES ('mike','lisa', 'lisa')
            ,('kevin','lisa', 'lisa')
            ,('lisa','ken', 'ken')
            ,('ken','scott', 'scott')
            ,('scott','chris', 'chris')
            ,('chris','', '')
        ) RawData(emp, sup, hierarchy)
    )
    ,Hier
AS (
    -- anchor level, no supervisor
    SELECT 1 AS lvl
        ,emp
        ,sup,
        cast (emp as varchar(255)) hierarchy
    FROM EmployeeSupervisor
    WHERE sup = ''

UNION ALL

-- recursive member
SELECT H.lvl + 1 AS lvl
    ,ES.emp
    ,ES.sup, 
    cast(ES.emp+', '+H.hierarchy as varchar(255))
FROM EmployeeSupervisor ES
INNER JOIN Hier H
    ON ES.sup = H.emp
WHERE H.lvl + 1 <= 5 -- max of 5 levels
    AND ES.sup != ''
)

SELECT *
FROM Hier

Then you would have something like this mike,lisa,ken,scott,chris in the last column. And then you need to split. For example using this answer

Is this ok for you?

PS: I think the cast is neccessary because of type missmatch, but you can change it to a higher number.

Well.. no, not exactly. It looks like you are expecting the last column to be `mike,lisa,ken,scott,chris`. If it was, I think this would work, but its actually being output as `lisa,ken` where emp = 'mike;' — Michael K, Aug 10 '20 at 19:32

David Browne - Microsoft · Answer 3 · 2020-08-10T20:20:48.467

Try recursing "up" from the employee level. And put additional debugging columns on an intermediate CTE, eg the "path" expression:

WITH EmployeeSupervisor
AS (
    SELECT cast(emp as varchar(20)) employee, cast(sup as varchar(20)) supervisor
    FROM (
        VALUES ('mike','lisa')
            ,('kevin','lisa')
            ,('lisa','ken')
            ,('ken','scott')
            ,('scott','chris')
            ,('chris',null)
        ) RawData(emp, sup)
    )
    , hier AS (
    SELECT 1 AS lvl
        ,employee
        ,supervisor
        , cast(concat('',employee,'->',supervisor) as varchar(max)) path
        ,cast(supervisor as varchar(20)) sup1
        ,cast(null as varchar(20)) sup2
        ,cast(null as varchar(20)) sup3
        ,cast(null as varchar(20)) sup4
        ,cast(null as varchar(20)) sup5
    FROM EmployeeSupervisor
    UNION ALL
    SELECT H.lvl + 1 AS lvl
        ,H.employee employee
        ,es2.employee
        , cast(concat('',h.path,'->',es.supervisor) as varchar(max)) path
        ,null sup1
        ,case when H.lvl + 1 = 2 then cast(es2.employee as varchar(20)) end sup2
        ,case when H.lvl + 1 = 3 then cast(es2.employee as varchar(20)) end sup3
        ,case when H.lvl + 1 = 4 then cast(es2.employee as varchar(20)) end sup4
        ,case when H.lvl + 1 = 5 then cast(es2.employee as varchar(20)) end sup5
    FROM  Hier H
    join EmployeeSupervisor es
        ON H.supervisor = ES.employee
    join EmployeeSupervisor es2
        ON es.supervisor = es2.employee
    WHERE H.lvl + 1 <= 5
)

SELECT 
    employee,
    MAX(sup1) sup1,
    MAX(sup2) sup2,
    MAX(sup3) sup3,
    MAX(sup4) sup4,
    MAX(sup5) sup5
FROM Hier
GROUP BY employee

which outputs:

employee             sup1                 sup2                 sup3                 sup4                 sup5
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
chris                NULL                 NULL                 NULL                 NULL                 NULL
ken                  scott                chris                NULL                 NULL                 NULL
kevin                lisa                 ken                  scott                chris                NULL
lisa                 ken                  scott                chris                NULL                 NULL
mike                 lisa                 ken                  scott                chris                NULL
scott                chris                NULL                 NULL                 NULL                 NULL

This got me 90% of the way there. Thanks. The last tweak needed for your code is to add a query that groups on the employee after the CTE: `SELECT employee,max(sup1) AS sup1,max(sup2) AS sup2,max(sup3) AS sup3,max(sup4) AS sup4 FROM Hier GROUP BY employee`. This way the output shown above is the actual output — Michael K, Aug 10 '20 at 20:14

score 1 · Accepted Answer · answered Aug 11 '20 at 08:33

If you want the hierarchy for all emps, you must start with all emps, not only the root. Then pivoting is simple.

WITH EmployeeSupervisor
AS (
    SELECT *
    FROM (
        VALUES ('mike','lisa')
            ,('kevin','lisa')
            ,('lisa','ken')
            ,('ken','scott')
            ,('scott','chris')
            ,('chris','')
        ) RawData(emp, sup)
    )
    ,Hier
AS (
    -- all employees
    SELECT 1 AS lvl
        ,emp
        ,sup
    FROM EmployeeSupervisor

    UNION ALL
    
    -- recursive supervisors
    SELECT H.lvl + 1 AS lvl
        ,H.emp
        ,ES.sup
    FROM EmployeeSupervisor ES
    JOIN Hier H
      ON ES.emp = H.sup
    WHERE H.lvl  < 5 -- max of 5 levels
      AND ES.sup <> ''
    )
SELECT *
FROM Hier 
pivot (max(sup) 
       for lvl in ([1], [2], [3], [4], [5])
      ) as pvt

See fiddle

score 0 · Answer 5 · answered Aug 10 '20 at 17:46

You recursive query needs to be fixed so it properly walks the relationships - also, you want to keep track of the original employee.

Then, the final step to pivot your resultset. For this, you can use conditional aggregation in the outer query:

WITH hier AS (
    SELECT 1 AS lvl
        ,emp
        ,sup
    FROM EmployeeSupervisor
    WHERE sup = ''
    UNION ALL
    SELECT H.lvl + 1 AS lvl
        ,H.emp
        ,ES.sup
    FROM EmployeeSupervisor ES
    INNER JOIN Hier H
        ON H.sup = ES.emp
    WHERE H.lvl + 1 <= 5
)
SELECT 
    emp,
    MAX(CASE WHEN lvl = 1 THEN sup END) sup1,
    MAX(CASE WHEN lvl = 2 THEN sup END) sup2,
    MAX(CASE WHEN lvl = 3 THEN sup END) sup3,
    MAX(CASE WHEN lvl = 4 THEN sup END) sup4,
    MAX(CASE WHEN lvl = 5 THEN sup END) sup5
FROM Hier
GROUP BY emp

Thanks for your response...I think the MAX(CASE WHEN ... approach is a good solution to part of the problem, but when I run this code with the "fixed" recursive subquery it only returns the highest level supervisor? (chris) — Michael K, Aug 10 '20 at 18:15

SQL Breakdown Hierarchy

5 Answers5