335
Chapter 13: Working with Hierarchies
13
CTEs can be self-referencing, enabling it to be referenced multiple times in the same
query. Because it is self-referencing, it therefore creates recursion, thus a recursive CTE. A
recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data
until the complete result set is returned.A recursive CTE is a common use for querying and returning hierarchical data. An example of
this is the HumanResources.Employee table in which there are two columns; EmployeeID
and ManagerID. The following query shows the structure of a recursive CTE by returning a
hierarchical list of employees. The list starts with the highest ranking employee.This example comes from the SQL Server 2005 AdventureWorks database because, as
mentioned earlier, the Hierarchyid data type was introduced in SQL Server 2008. Thus, in
order to illustrate hierarchical data “pre” hierarchy id, the use of an earlier version of the
AdventureWorks database is necessary.WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM HumanResources.Employee AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;
GOManagerID EmployeeID Title EmployeeLevel
--------- ---------- -------------- -------------NULL 109 Chief Executive Officer 0
3 4 Senior Tool Designer 3
3 9 Design Engineer 3
3 11 Design Engineer 3
3 158 Research and Development Manager 3
3 263 Senior Tool Designer 3
3 267 Senior Design Engineer 3
3 270 Design Engineer 3
6 2 Marketing Assistant 2
6 46 Marketing Specialist 2
6 106 Marketing Specialist 2
6 119 Marketing Specialist 2
6 203 Marketing Specialist 2c13.indd 335c13.indd 335 7/30/2012 4:47:05 PM7/30/2012 4:47:05 PM
http://www.it-ebooks.info