Recursive Query with CTE II

You can think of a number of scenarios where running a recursive query can be the answer. This example covers a recursive query on a department table, so that you can build an organization chart. It uses a Common Table Expression (CTE).

The department table in this example has a so-called parent. This column shows that the table Afdeling (department) refers to itself.

{highslide type=“img” url=“artikelen/tsql/recursieve_query_met_cte/common_table_expression_00.jpg” width=400 captionText=‘CTE’}{/highslide}

The parent of the department with afdelingID 2 is the department with afdelingID 1, and so on. Based on this table I want to show how the departments relate to each other, in other words which department has which departments under it. With a Common Table Expression (CTE) with a UNION you can answer this question easily.

**CTE
**A CTE is nothing more than an expression that returns a temporary result set that you can refer to more than once. Because you can access this result set more than once, it is an excellent tool for a recursive query.

The syntax of a CTE is fairly simple:

{geshi xml:lang=“tsql” lines=“false”}
WITH expression_name [ ( column_name [,…n] ) ]
AS
( CTE_query_definition )

SELECT
FROM expression_name{/geshi}

The WITH clause starts the CTE. After the WITH you give the name of the CTE and then the names of the columns. After the column definition comes the AS and then the query that builds the CTE.

After the definition of the CTE comes a SELECT statement to get the data from the CTE.
The example below is a CTE based on the AdventureWorks database.

{geshi xml:lang=“tsql” lines=“false”}

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT 
SalesPersonID,
COUNT(*),
MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

)
SELECT
E.EmployeeID,
OS.NumberOfOrders,
OS.MaxDate,
E.ManagerID,
OM.NumberOfOrders,
OM.MaxDate
FROM HumanResources.Employee  AS E
INNER JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID
GO{/geshi}

The CTE returns a result set with the total number of orders and the most recent order date per salesperson. The second SELECT statement then refers to the CTE twice. Once to get the number of orders and the most recent date per employee, and a second time to do the same for the managers.

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate

-––––– ––––––– ––––– ——— ––––––– –––––

268         48             2004-06-01 273       NULL           NULL

275         450            2004-06-01 268       48             2004-06-01

276         418            2004-06-01 268       48             2004-06-01

277         473            2004-06-01 268       48             2004-06-01

Recursive Query
Based on the Afdeling table from the example above, we have to find out which department falls under which department, to build an organization chart.

First a statement to build the CTE:

{geshi xml:lang=“tsql” lines=“false”}
WITH AfdelingTree (AfdelingID, ParentAfdelingID, Omschrijving, AfdelingsLevel, SortKey)
AS

(

SELECT
AfdelingID, ParentAfdelingID, Omschrijving, 0, cast(AfdelingID AS VARBINARY(900))
FROM Afdeling
WHERE ParentAfdelingID IS NULL

UNION ALL

SELECT
a.AfdelingID, a.ParentAfdelingID, a.Omschrijving, b.AfdelingsLevel+1,
CAST(b.SortKey + CAST (a.AfdelingID AS BINARY(4)) AS VARBINARY(900))
FROM Afdeling as a
INNER JOIN AfdelingTree as B on a.ParentAfdelingID = b.AfdelingID
)  {/geshi}

The CTE is called AfdelingTree and defines a number of columns.
Then we build the SELECT statement that defines the first department, where the ParentAfdelingID is NULL. This is the department at the top of the organization chart and defines the highest level. Here we also define a sortkey that we use later for an ORDER BY.

In the second SELECT statement we look for the child departments that have the ParentAfdelingID of the previous query. We do this with a JOIN on the CTE in the second statement: the self-reference. This part of the query repeats until no records are left. In other words: until all departments are handled and the parent-child organization chart is built. We also add the sortkey to the sortkey of the parent, to get the levels in the right order.

Now that the CTE is built, all that is left is a SELECT statement to get the organization chart from the CTE.

{geshi xml:lang=“tsql” lines=“false”}
SELECT
REPLICATE(’–>    ’, AfdelingsLevel) + ‘(’ + (CAST(AfdelingID AS VARCHAR(10))) + ’) ’+ Omschrijving AS AfdelingOmschrijving,
AfdelingID
ParentAfdelingID,
Omschrijving,
AfdelingsLevel,
SortKey
FROM AfdelingTree
ORDER BY SortKey{/geshi}

With the REPLICATE function we indent per department level, so you can see the hierarchy of the organization chart in SQL Server. The query gives this result:

{highslide type=“img” url=“artikelen/tsql/recursieve_query_met_cte/common_table_expression_01.jpg” width=600 captionText=‘CTE’}{/highslide}