AdventureWorks Hierarchy

Question

What is the query to determine the full hierarchy of an employee's managers?

example: Alex is the input

Sara (Manager - Level 1) James(Manager - Level 2) David(Manager - Level 3) Alex(Simple Employee)

Answer 1

DECLARE @Hierarchy TABLE (
    ID int,
    Manager int,
    Level int);


WITH Hierarchy(ID, Manager, Level) AS (
    SELECT EmployeeID, ManagerID, 0
    FROM HumanResources.Employee

    JOIN Person.Contact
    ON Contact.ContactID = Employee.ContactID

    WHERE FirstName = N'Alex'


    UNION ALL

    SELECT EmployeeID, ManagerID, Level + 1
    FROM HumanResources.Employee

    JOIN Hierarchy
    ON Manager = EmployeeID)

INSERT @Hierarchy

SELECT *
FROM Hierarchy


DECLARE @HighestLevel int

SELECT @HighestLevel = MAX(Level)
FROM @Hierarchy

DECLARE Hierarchy CURSOR FOR
    SELECT
        FirstName + 
        N'(' + 
        CASE
            WHEN Level = 0
            THEN N'Simple Employee'
            ELSE
                N'Manager - Level ' + 
                CAST(@HighestLevel - Level + 1 AS varchar) END + 
        N')'
    FROM HumanResources.Employee

    JOIN @Hierarchy
    ON ID = EmployeeID

    JOIN Person.Contact
    ON Contact.ContactID = Employee.ContactID

    ORDER BY Level DESC


DECLARE @Employee nvarchar(max)
DECLARE @Result nvarchar(max)

SET @Result = N''


OPEN Hierarchy

FETCH Hierarchy
INTO @Employee

WHILE @@FETCH_STATUS = 0 BEGIN
    SET @Result = @Result + N' ' + @Employee

    FETCH Hierarchy INTO @Employee
END

CLOSE Hierarchy
DEALLOCATE Hierarchy


PRINT @Result