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

Reddy, T 25 Reputation points
2023-06-19T11:55:57.88+00:00

I have a table in Azure Synapse Analytics.

The structure is emp (emp_Win int, supervisor_id int)

the input data is like below.

User's image

I required the output data like below.

User's image

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 manager 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

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

User's image

  • 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.

User's image

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,186 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2023-06-22T08:45:22.0433333+00:00

    Hi Reddy, T ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your question, you are trying to get the level for the employees based on their hierarchy. However, you want the query to be dynamic where column numbers should be based on the level of employees.

    You can make use of CTE(common table expression) and perform a self join for the emp table in order to create the hierarchy levels like below:

    User's image

    This would give the hierarchy in a single output column 'empLevel' . It still needs to be modified to make it dynamic to split the values into multiple columns .

    I will update you for the dynamic part after I work on it. Hope it helps. Thankyou


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.