Execute the following SQL Server T-SQL script in Management Studio Query Editor to create an orgchart for level 0 (CEO) and level 1 (executives).

-- SQL Server Common Table Expression - CTE - Tree processing

-- SQL recursive CTE - SQL organizational chart - SQL union all

USE AdventureWorks;

WITH Executive(EmployeeID)

     AS (SELECT DISTINCT ManagerID

         FROM   HumanResources.Employee

         WHERE  ManagerID IS NOT NULL),

     OrgChart(ManagerID,EmployeeID,EmployeeLevel)

     AS (SELECT ManagerID,

                EmployeeID,

                0 AS EmployeeLevel

         FROM   HumanResources.Employee

         WHERE  ManagerID IS NULL

         UNION ALL

         SELECT e.ManagerID,

                e.EmployeeID,

                EmployeeLevel + 1

         FROM   HumanResources.Employee e

                INNER JOIN OrgChart d

                  ON e.ManagerID = d.EmployeeID)

SELECT   replicate('_',(EmployeeLevel) * 4) + FirstName + ' ' + LastName AS Executive

FROM     Executive m

         JOIN OrgChart oc

           ON m.EmployeeID = oc.EmployeeID

         JOIN HumanResources.Employee e

           ON oc.EmployeeID = e.EmployeeID

         JOIN Person.Contact c

           ON c.ContactID = e.ContactID

WHERE    EmployeeLevel < 2

ORDER BY EmployeeLevel,

         Executive;

GO

/* Results

Executive

Ken Sánchez

____Brian Welcker

____David Bradley

____James Hamilton

____Jean Trenary

____Laura Norman

____Terri Duffy

*/