All the Bosses - Traversing Tree Bottom Up for Chain of Command

By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, MCDBA, MCITP

April 8, 2007

SQL Server 2005 recursive CTE  is frequently used to create organizational chart, the most typical use of the tree processing capability.  It can also be used to recursively traverse a tree from the leaf nodes to the root node. In the case of the Employee table in AdventureWorks database, the tree is the single parent type. Bottom traversing can even be carried out by simple WHILE loop, however, the recursive CTE facility can do with UNION ALL thus avoiding the traditional type looping.

In the script below, the staff level (not managers) employees of AdventureWorks are identified by  not being the manager of anybody in cteStaff. For each staff level employee the fnAllTheBosses function (UDF) called which contains the SQL Server 2005 recursive CTE.

This is the T-SQL code sample:
 

-- Create user-defined scalar-valued function

USE AdventureWorks;

GO

CREATE FUNCTION fnAllTheBosses

               (@EmployeeID INT)

RETURNS VARCHAR(512)

AS

  BEGIN

    DECLARE  @Bosses  AS VARCHAR(512)

    SET @Bosses = '';

    WITH EmployeeCTE(Name,EmployeeID,ManagerID)

         AS (SELECT FirstName + ' ' + LastName,

                    EmployeeID,

                    ManagerID

             FROM   HumanResources.Employee AS E

                    JOIN Person.Contact AS C

                      ON C.ContactID = E.ContactID

             WHERE  EmployeeID = @EmployeeID

             UNION ALL

             SELECT C.FirstName + ' ' + C.LastName,

                    HRE.EmployeeID,

                    HRE.ManagerID

             FROM   HumanResources.Employee AS HRE

                    JOIN Person.Contact AS C

                      ON C.ContactID = HRE.ContactID

                    JOIN EmployeeCTE AS E

                      ON E.ManagerID = HRE.EmployeeID)

    SELECT @Bosses = @Bosses + Name + '; '

    FROM   EmployeeCTE

    RETURN @Bosses

  END

GO

-- Test UDF

SELECT dbo.fnAllTheBosses(250)

GO

/*

Krishna Sunkammurali; Eric Gubbels; Peter Krebs; James Hamilton; Ken Sánchez;

*/

-- Generate chain of command for all non-supervisory staff

WITH cteStaff([EmployeeName],EmployeeID)

     AS (SELECT [Name] = FirstName + ' ' + LastName,

                EmployeeID

         FROM   HumanResources.Employee HRE

                JOIN Person.Contact C

                  ON C.ContactID = HRE.ContactID

         WHERE  EmployeeID NOT IN (SELECT ManagerID

                                   FROM   HumanResources.Employee

                                   WHERE  ManagerID IS NOT NULL))

SELECT   [Employee & Bosses] = dbo.fnAllTheBosses(EmployeeID)

FROM     cteStaff

ORDER BY EmployeeName

GO

This is the partial result set:

Employee & Bosses   Michael Raheem; Dylan Miller; Roberto Tamburello; Terri Duffy; Ken Sánchez;    Michael Rothkugel; David Hamilton; Peter Krebs; James Hamilton; Ken Sánchez;    Michael Sullivan; Roberto Tamburello; Terri Duffy; Ken Sánchez;

Related article:

Using Common Table Expressions