CODEWITHSUNDEEP
sql-servert-sql
Mou
Mou

Reputation: 16282

SQL Server: Hierarchical query with depth

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's level would be 0 because he is top and Katie's level will be 1 and John's 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

Upvotes: 0

Views: 1261

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

Since this is Node-to-Top, notice in the final select [Level]

;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

Upvotes: 2

Related Questions