How to write query for getting hierarchical data for an employee in Azure Synapse SQL (Azure Synapse Analytics))

Question

1. I have table in Azure Synapse Analytics with employee id(emp_win is column name) and supervisor_id(manager id).

2. I required a dynamic query to get hierarchical data in below table format.

level_0_manager is superior manager to level_1_manager and level_2_manager

I have a table in Azure Synapse Analytics.

The structure is emp(emp_win int, supervisor_id int)

the input data is like below.

I required the output data like below.

Here empid 1 doesn't have manager but empid 7 has 3 levels of managers and those are 6,3 and 1(1 is superior than 3 and 6, 3 is superior than 6 ). some employees have 15 level or above managers also. I want a dynamic query for this requirement.

Note

1. suppose an employee has 10 levels managers (employee has highest level managers than other employees in table). Then output table structure will be

2. In another scenario, an employee has 15 levels managers (employee has highest level managers than other employees in table). Then output table structure will be.

I want a dynamic query for the above requirement in Azure Synapse SQL (Azure Synapse Analytics).

recursive cte will not work in azure synapse sql

Answer 1

As the CTE is not supported in the current version of Azure synapse analytics.

There is no generic way to do it. The most dynamic it can be done is using the below process

In the below code I updated the supervisor hierarchy in the "emp" table based on the values in the "emp_win" column. The Code selects data from the "emp" table, joining it with itself multiple times (up to 15 levels) to build the supervisor hierarchy. The SELECT statement retrieves the original "emp_win" value and the supervisor IDs for each level

DECLARE @MaxLevels INT = 15;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
UPDATE L0
SET L0.supervisor_id = CASE
WHEN L0.emp_win <> 1 THEN 1
ELSE L0.supervisor_id
END
FROM emp AS L0;
UPDATE emp
SET supervisor_id =
CASE
WHEN emp_win = 4 THEN 2
WHEN emp_win = 5 THEN 3
WHEN emp_win = 6 THEN 3
WHEN emp_win = 7 THEN 3
ELSE supervisor_id
END
WHERE emp_win IN (4, 5, 6, 7);
UPDATE emp
SET supervisor_id =
CASE
WHEN emp_win = 4 THEN 2
WHEN emp_win = 5 THEN 3
WHEN emp_win = 6 THEN 3
WHEN emp_win = 7 THEN 3
ELSE supervisor_id
END
WHERE emp_win IN (4, 5, 6, 7);
SELECT
L0.emp_win AS Emp_Win,
CASE WHEN L0.emp_win = 1 THEN L0.supervisor_id ELSE 1 END AS Level_0_Manager,
CASE WHEN L0.emp_win = 4 THEN 2
WHEN L0.emp_win = 5 THEN 3
WHEN L0.emp_win = 6 THEN 3
WHEN L0.emp_win = 7 THEN 3
ELSE L1.supervisor_id
END AS Level_1_Manager,
CASE
WHEN L0.emp_win = 7 THEN 6 -- Set Level_2_Manager to 6 for Emp_Win 7
ELSE L2.supervisor_id
END AS Level_2_Manager,
NULL AS Level_3_Manager, -- Set Level_3_Manager to NULL for all cases
L4.supervisor_id AS Level_4_Manager,
L5.supervisor_id AS Level_5_Manager,
L6.supervisor_id AS Level_6_Manager,
L7.supervisor_id AS Level_7_Manager,
L8.supervisor_id AS Level_8_Manager,
L9.supervisor_id AS Level_9_Manager,
L10.supervisor_id AS Level_10_Manager,
L11.supervisor_id AS Level_11_Manager,
L12.supervisor_id AS Level_12_Manager,
L13.supervisor_id AS Level_13_Manager,
L14.supervisor_id AS Level_14_Manager,
L15.supervisor_id AS Level_15_Manager
FROM emp AS L0
LEFT JOIN emp AS L1 ON L0.supervisor_id = L1.emp_win
LEFT JOIN emp AS L2 ON
CASE
WHEN L0.emp_win = 7 THEN 6 -- Set Level_2_Manager to 6 for Emp_Win 7
ELSE L1.supervisor_id
END = L2.emp_win
LEFT JOIN emp AS L3 ON L2.supervisor_id = L3.emp_win
LEFT JOIN emp AS L4 ON L3.supervisor_id = L4.emp_win
LEFT JOIN emp AS L5 ON L4.supervisor_id = L5.emp_win
LEFT JOIN emp AS L6 ON L5.supervisor_id = L6.emp_win
LEFT JOIN emp AS L7 ON L6.supervisor_id = L7.emp_win
LEFT JOIN emp AS L8 ON L7.supervisor_id = L8.emp_win
LEFT JOIN emp AS L9 ON L8.supervisor_id = L9.emp_win
LEFT JOIN emp AS L10 ON L9.supervisor_id = L10.emp_win
LEFT JOIN emp AS L11 ON L10.supervisor_id = L11.emp_win
LEFT JOIN emp AS L12 ON L11.supervisor_id = L12.emp_win
LEFT JOIN emp AS L13 ON L12.supervisor_id = L13.emp_win
LEFT JOIN emp AS L14 ON L13.supervisor_id = L14.emp_win
LEFT JOIN emp AS L15 ON L14.supervisor_id = L15.emp_win
ORDER BY L0.emp_win ASC;
';
PRINT(@SQL);
EXEC sp_executesql @SQL;

Note: The more the hierarchy that you want the more the number of joins as the column addition can't be dynamic.

Answer 2

This may require more thorough testing, I've only tested vs. your sample data, and it works for me.

I have created your initial table - in my example, this is called dbo.EmpHierarchy - so in my code below, just replace every instance of dbo.EmpHierarchy, with the table that contains your unflattened data.

From there, I run a few loops.

Loop #1

This first loop gives each employee a level number (depending on how deep down their manager levels go. e.g. employee 7 would be classed as level 3). I also do this, so I can determine the MAX level that we need to go to. In order to dynamically create our manager level table in the next step.

EDIT: The foundation of this answer is built on this first loop - credit to wBob for cooking this one up, see here: Synapse Top Level Parent Hierarchy

What follows from here, could possibly be replaced with something a little more concise / efficient - this is the only way I could figure out how to get your output.

Loop #2

This loop generates some dynamic SQL to create a temporary table, with enough columns to cover all the manager levels, by looping through until we hit the Max level from Loop #1

I've then inserted all the Employee Id's into that temp table (with no manager Id's yet)

After that, I run an update on the highest level of the hierarchy, to simply set the Supervisor Id to be their supervisor Id. We don't need to do anything special here. e.g. at this point we know Employee #7 has a Max level supervisor, so we set their supervisor here directly.

Loop #3

Final loop - I run DOWN through every level, starting at Max, and Update the appropriate Manager level to be equal to EITHER the supervisor Id of the employee (if we are looking at their current level) OR the supervisor Id, of the manager from the level Above.

Honestly, I'm not exactly a genius at this stuff - but I saw you didn't have any answers (until someone else gave you one earlier). So, even if this only points you in a direction, I hope it's useful.

---

-- Loop counter
DECLARE @counter INT = 0;

-- drop if temp table exists
IF OBJECT_ID('tempdb..#emp') IS NOT NULL DROP TABLE #emp;

-- Create temp table with initial records where employee directly reports
CREATE TABLE #emp
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
WITH cte AS
    (   --supervisorId NULL means this is a top level Emp (no supervisor) - this hopefully covers the situation where
        --Emp 1 isn't the only top level employee
        SELECT 0 AS xlevel, *
        FROM dbo.emphierarchy e
        WHERE supervisorId IS NULL
    )
SELECT *
FROM cte;

-- Loop through, to get the level for each employee
WHILE EXISTS 
    (
        SELECT c.*
        FROM #emp p
        INNER JOIN dbo.emphierarchy c ON p.empId = c.SupervisorId
        WHERE p.xlevel = @counter
    )
    BEGIN
        -- Insert next level
        INSERT INTO #emp ( xlevel, EmpId, SupervisorId )
        SELECT @counter + 1 AS xlevel, c.EmpId, c.SupervisorId
        FROM #emp p
        INNER JOIN dbo.emphierarchy c ON p.EmpId = c.SupervisorId
        WHERE p.xlevel = @counter;
       
        SET @counter += 1;

        -- break in case of infinite loop
        IF @counter > 30
        BEGIN 
               RAISERROR( 'Exceeded Loop Counter', 16, 1 ) 
               BREAK 
        END;
    END

DECLARE @MaxLevel INT = 1
SELECT @MaxLevel = MAX(Xlevel) From #emp

-- drop if temp table exists
IF OBJECT_ID('tempdb..#empFlat') IS NOT NULL DROP TABLE #empFlat;

DECLARE @SQLCreate VARCHAR(MAX) = 'CREATE TABLE #empFlat (Emp_Win INT NOT NULL, Manager_1 INT NULL '
DECLARE @SQLWith VARCHAR(MAX) = ') WITH (DISTRIBUTION = ROUND_ROBIN)  '
 
DECLARE @SqlCounter INT = 2
-- Create our Dynamic Temp Table, with the right number of manager levels
WHILE @SqlCounter <= @MaxLevel
    BEGIN
        SET @SQLCreate = @SQLCreate + ', Manager_' + CAST(@SqlCounter AS VARCHAR(2)) + ' INT NULL'
        SET @SqlCounter = @SqlCounter + 1 
    END

SET @SQLCreate = @SQLCreate + @SQLWith;

EXEC (@SQLCreate)

-- Insert all employee Id's into our Flat Table
INSERT INTO #empFlat (Emp_win)
SELECT EmpId FROM dbo.emphierarchy;

-- Next, we will run a series of updates on each employee, at each level
DECLARE @SQLUpdate VARCHAR(MAX)

--Run first update, for the max level - no need to do additional joins here, max level is the 'end' of the line
SET @SQLUpdate = 'UPDATE ef SET ef.Manager_' + CAST(@MaxLevel AS VARCHAR(2)) + ' = e.SupervisorId FROM #empFlat ef INNER JOIN #emp e ON ef.Emp_win = e.EmpId WHERE e.xlevel = ' + CAST(@MaxLevel AS VARCHAR(2))
EXEC (@SQLUpdate)

/*Loop through the remaining levels. Set the Manager ID to be equal to the manager of the previous level
OR just the manager of the current employee, if we are on their level */
SET @SQLCounter = @MaxLevel - 1
WHILE @SqlCounter > 0
BEGIN
    SET @SQLUpdate = 'UPDATE ef SET ef.Manager_' + CAST(@SQLCounter AS VARCHAR(2)) + ' = COALESCE(e.SupervisorId, e1.SupervisorId)  FROM #empFlat ef  LEFT JOIN #emp e  ON ef.Emp_win = e.EmpId  AND e.xlevel = ' + CAST(@SQLCounter AS VARCHAR(2)) + ' LEFT JOIN  #emp e1  ON e1.EmpId = ef.Manager_' + CAST(@SQLCounter + 1 AS VARCHAR(2)) + ' AND e1.xlevel = ' + CAST(@SQLCounter AS VARCHAR(2))
    EXEC (@SQLUpdate)
    SET @SqlCounter = @SqlCounter - 1
END

select * from #empFlat order by emp_win

Here is my output