All the Bosses - Traversing Tree Bottom Up

By Kalman Toth, M.Phil., M.Phil., 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 called which contains the SQL Server 2005 CTE which is recursive inside the function.

This is the code sample:
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

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

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;