multi-parent hierarchy propagation in SQL/DAX

Question

Suppose I have a table that describes primary and secondary reporting lines for each member of staff. Let's imagine an organizational structure where the CEO, employee 0, has 2 managers (1 and 2) reporting to him.

Manager 2 has 2 staff in her team (3 and 4), however staff member 4 actually works in Manager 1's timezone, so while he has 2 as his primary report, he also reports to Manager 1 as a Secondary report so that 1 can fullfil normal fiduciary managerial obligations (provide support, etc.).

In addition to taking a secondary management role for employee 4, Manager 2 also has a team member reporting to him (5).

Edit: To illustrate the multi-parent problem, let's give team member 4 an intern, staff member 6. Team member 6 is now the subordinate of both managers 1 and 2 - the latter being inherited through the secondary reporting line.

The organizational structure would look like this:

+--+-------+---------+

|ID|Primary|Secondary|

|0 |NULL   |NULL     |

|1 |0      |NULL     |

|2 |0      |NULL     |

|3 |1      |NULL     |

|4 |1      |2        |

|5 |2      |NULL     |

|6 |4      |NULL     |

+--+-------+---------+

Now I want to expand this into a SQL view that gives me a list of people below any given staff member, covering both primary and secondary reports. So for staff member 2 (the manager with a primary and secondary report), I would expect to see team members 4 and 5, and for the CEO (0) I'd expect to see ever staff member other than the CEO. Our new intern, 6, is the subordinate of the CEO, managers 1 and 2, as well as his direct manager, 4.

This would look like this:

+--+-----------+

|ID|Subordinate|

|0 |1          |

|0 |2          |

|0 |3          |

|0 |4          |

|0 |5          |

|0 |6          |

|1 |3          |

|1 |4          |

|1 |6          |

|2 |4          |

|2 |5          |

|2 |6          |

|4 |6          |

+--+-----------+

How would I achieve this in SQL? I'm thinking some kind of OUTER APPLY operation on the ID but I'm struggling to get my head around the reentrancy that would be required (I think) to solve this. My background is in procedural programming, which I think is part of the reason I'm struggling here.

NB: An obvious question that I'd like to anticipate here is "Surely this is an XY problem - why on earth would you want to do this?"

I want to use row-level security in PowerBI to give each staff member access to certain information about individuals below them in the organizational structure. Unfortunately, RLS doesn't permit the execution of stored procedures per individual, so I'm stuck with doing this combinatorial expansion and then simply filtering the above table based on the login.

Having said that, I'm open to better ways of approaching this problem.

Answer 1

• In order to get the result, the easiest way is to use a recursive CTE.

• Let's discuss an example below I divided the work into two CTEs.

• The first one transforms the set into a pair of managers and subordinates. 

• The second CTE gets all results from the first and then joins to itself using UNION.

• This will keep repeating until there are no matches that can be made,

where the manager from the first CTE is a subordinate in the recursive CTE. 

• It is possible that a subordinate has more than one manager, duplicate rows can be returned for each ancestor as DISTINCT is used when returning results from the recursive CTE.

WITH all_reports AS (

    SELECT [Primary] [ManagerID], ID [Subordinate]

    FROM tbl

    WHERE [Primary] IS NOT NULL

    UNION

    SELECT [Secondary], ID

    FROM tbl

    WHERE [Secondary] IS NOT NULL

)

, recursive_cte AS (

    SELECT ManagerID, Subordinate

    FROM all_reports

    UNION ALL

    SELECT ancestor.ManagerID, descendant.Subordinate

    FROM recursive_cte ancestor

    INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate

)

SELECT DISTINCT ManagerID, Subordinate

FROM recursive_cte

• If we want the distance between manager and subordinate then  we need to rewrite the recursive CTE as follows:

SELECT ManagerID, Subordinate, 1 [Distance]

FROM all_reports

UNION ALL

SELECT ancestor.ManagerID, descendant.Subordinate, ancestor.Distance + 1

FROM recursive_cte ancestor

INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate