query for hierarchy

elsvieta 346 Reputation points
2023-05-16T12:28:05.0666667+00:00

hi all,

my employee table is as follows:

empID orgLevel supervisorID
1222 0 4567
1233 0 4567
4567 1 8901
5678 1 8901
8901 2 9012

and so on.

I am trying to write a query that would give me a result like:

empIDChain empID orgLevel supervisorID

1222 1222 0 4567
1222 4567 1 8901
1222 8901 2 9012
1222 9012 3 null
1233 1233 0 4567
1233 4567 1 8901
1233 8901 2 9012
1233 9012 3 null
4567 4567 1 8901
4567 8901 2 9012
4567 9012 3 null
......
8901 8901 2 9012
8901 9012 3 null
9012 9012 3 null

So, from a table that has rows showing an employee id and its supervisor ID, I need a table showing all the rows for each employee and the full chain of command up to the last level.

Thank you very much,

elsvieta

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,281 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sedat SALMAN 13,345 Reputation points
    2023-05-16T12:59:57.7033333+00:00

    I have added the following sample query

    WITH EmployeeHierarchy AS (
        SELECT empID AS empIDChain, empID, orgLevel, supervisorID
        FROM EmployeeTable 
        WHERE supervisorID IS NOT NULL
        UNION ALL
        SELECT E.empIDChain, ET.empID, ET.orgLevel, ET.supervisorID
        FROM EmployeeHierarchy E
        JOIN EmployeeTable ET ON E.supervisorID = ET.empID
    )
    SELECT * FROM EmployeeHierarchy;
    
    
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful