locked
SQL Server: Hierarchical query with depth RRS feed

  • Question

  • User264732274 posted

    i have to show employee and his manager name with depth.

    if simon is top manager and katie's manager is simon and john's manager is katie then simon level would be 0 because he is top and katie level will be 1 and john level will be 2. i tried below way but not getting desired output. please tell me what to rectify in code.

    my code

    Declare @ID INT
    SET @ID=6;
    
    WITH EmployeeCTE AS
    (
        Select ID,Name,MgrID, 0 as level FROM Employee
        WHERE ID=@ID
    
        UNION ALL
    
        Select emp.ID,emp.Name,emp.MgrID, level+1 as level FROM Employee emp
        JOIN EmployeeCTE on emp.ID = EmployeeCTE.MgrID
    )
    
    --select * from EmployeeCTE
    
    Select e1.Name,ISNULL(e2.Name,'Top BOSS') as [Manager Name], e2.level from EmployeeCTE e1 left join EmployeeCTE e2
    on e1.MgrID=e2.ID

    Saturday, November 19, 2016 5:18 PM

Answers

  • User264732274 posted

    solved it

    ;WITH EmployeeCTE AS
    (
        Select ID,Name,MgrID, 0 as level FROM @Employee
        WHERE ID=3
        UNION ALL
        Select r.ID,r.Name,r.MgrID, level+1 as level 
        FROM @Employee r
        JOIN EmployeeCTE p on r.ID = p.MgrID
    )
    Select e1.Name
          ,ISNULL(e2.Name,'Top BOSS') as [Manager Name]
          ,row_number() over (order by e1.level  desc) as [Level]
     from EmployeeCTE e1 
     left join EmployeeCTE e2 on e1.MgrID=e2.ID 

    Returns

    Name    Manager Name    Level
    Simon   Top BOSS        1
    Katie   Simon           2
    John    Katie           3
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 19, 2016 5:51 PM

All replies

  • User264732274 posted

    solved it

    ;WITH EmployeeCTE AS
    (
        Select ID,Name,MgrID, 0 as level FROM @Employee
        WHERE ID=3
        UNION ALL
        Select r.ID,r.Name,r.MgrID, level+1 as level 
        FROM @Employee r
        JOIN EmployeeCTE p on r.ID = p.MgrID
    )
    Select e1.Name
          ,ISNULL(e2.Name,'Top BOSS') as [Manager Name]
          ,row_number() over (order by e1.level  desc) as [Level]
     from EmployeeCTE e1 
     left join EmployeeCTE e2 on e1.MgrID=e2.ID 

    Returns

    Name    Manager Name    Level
    Simon   Top BOSS        1
    Katie   Simon           2
    John    Katie           3
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 19, 2016 5:51 PM
  • User-62323503 posted

    Refer this post:

    http://www.itdeveloperzone.com/2011/03/hierarchy-of-employees-in-sql-server.html

    Sunday, November 20, 2016 7:49 AM