The Organization Chart of AdventureWorks with Recursive CTE-s

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

October 20, 2007

CTE-s can be used for recursive queries where the underlying table has a tree structure. The Employee table in the HumanResources schema has the ManagerID which self-references the same table. The ManagerID (Foreign Key) points to another EmployeeID (Primary Key) in the same table. The exception is the CEO who does not have a ManagerID. It is set to NULL. The recursion starts with the CEO as the anchor (root) member and expands all the way down the last staff member by the application of the recursive member.

Needless to say that Organizational Chart type of reports are good career boosters. They do demonstrate the incredibly power of Microsoft SQL Server 2005 T-SQL.

This is the code sample:

-- Microsoft SQL Server T-SQL recursive Common Table Expression - CTE

-- T-SQL organinational chart, orgchart, tree processing

-- MSSQL inner join, left join, self join

-- T-SQL select from select, derived table, subselect, subquery

USE AdventureWorks;

WITH cteEmployeeManager(EmployeeID,Name,ManagerID,LEVEL)

     AS (-- Anchor (root) Member (AM)

         SELECT EmployeeID,

                c.LastName + ', ' + c.FirstName,

                ManagerID,

                0

         FROM   Person.Contact c

                JOIN HumanResources.Employee e

                  ON c.ContactID = e.ContactID

         WHERE  ManagerID IS NULL

         UNION ALL

         -- Recursive Member (RM)

         SELECT e.EmployeeID,

                c.LastName + ', ' + c.FirstName,

                e.ManagerID,

                M.LEVEL + 1

         FROM   Person.Contact c

                INNER JOIN HumanResources.Employee e

                  ON c.ContactID = e.ContactID

                INNER JOIN cteEmployeeManager AS M

                  ON E.ManagerID = M.EmployeeID)

SELECT   CEO = l0.Name,

         Executive = l1.Name,

         Manager = l2.Name,

         Supervisor = ISNULL(l3.Name,''),

         Staff = ISNULL(l4.Name,'')

FROM     (SELECT Name,

                 EmployeeID,

                 ManagerID

          FROM   cteEmployeeManager

          WHERE  LEVEL = 0) l0

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 1) l1

           ON l1.ManagerID = l0.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 2) l2

           ON l2.ManagerID = l1.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 3) l3

           ON l3.ManagerID = l2.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 4) l4

           ON l4.ManagerID = l3.EmployeeID

ORDER BY l1.Name,

         l2.Name,

         l3.Name,

         l4.Name

GO

This is the result set: