There are a number of scenarios to consider when running a recursive query results can offer. This example focuses on a recursive query within a department table which a chart can be built. It uses a Common Table Expression (CTE).

Within the department table in this example there is a known parent. This column does indicate that within the department table there is a self-reference.

The department has a parent with afdelingID 2 Division 1 and department afdelingID so on. Based on this table I want to clarify the relationship between the various departments mutually, ie, which department has the department under him. Using a Common Table Expression (CTE) with a UNION, this question is easily answered.

CTE
A CTE is nothing but an expression that returns a temporary resultset which can be referred to several times. Resultset because this can be approached several times, this is an excellent means to do a recursive query.

The syntax for a CTE is quite simple:

  expression_name [ ( column_name [ ,... n ] ) ] WITH expression_name [(column_name [, ... n])] 
 AS 
 CTE_query_definition ) (CTE_query_definition) 

  SELECT 
 expression_name FROM expression_name 

The WITH clause enables the start of construction of the CTE. Following WITH the name of the CTE and then given the names of the columns. After defining the columns, the AS and then the query that builds the CTE.

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

  SalesPersonID, NumberOfOrders, MaxDate ) WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
 AS
 (
 SELECT
  SalesPersonID,
  ( * ) , COUNT (*),
  ( OrderDate ) MAX (OrderDate)
 Sales. SalesOrderHeader FROM Sales. Sales Order Header
 SalesPersonID GROUP BY SalesPersonID 
  )
 SELECT 
  , E. EmployeeID, 
  , OS. NumberOfOrders, 
  , OS. MaxDate,
  , E. ManagerID, 
  , OM. NumberOfOrders, 
  OM. MaxDate
 HumanResources. Employee AS E FROM HR. Employee AS E
 Sales_CTE AS OS ON E. EmployeeID = OS. SalesPersonID INNER JOIN AS OS ON E. EmployeeID Sales_CTE OS =. SalesPersonID
 Sales_CTE AS OM ON E. ManagerID = OM. SalesPersonID LEFT OUTER JOIN Sales_CTE AS OM ON E. ManagerID = OM. SalesPersonID
 E. EmployeeID ORDER BY E. EmployeeID
 GO 

The CTE provides a resultset back where the total number of orders with the most recent order date per sales person is returned. Then there are two times to the accumulated CTE referred to in the second SELECT statement. Once for each employee the number of orders with the most recent date to pick and half times to do the same but for the managers.

EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate

----------- -------------- ---------- --------- ------ -------- ----------

268 48 2004 273 -06 to 01 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 Department table in the above example should identify the section under which department is to create a chart to build.

First, a statement to the CTE to build:

  AfdelingTree ( AfdelingID, ParentAfdelingID, Omschrijving, AfdelingsLevel, SortKey ) WITH Tree Department (AfdelingID, ParentAfdelingID, Description, Division Level, SortKey)
 AS

 (

 SELECT 
 , CAST ( AfdelingID AS VARBINARY ( 900 ) ) AfdelingID, ParentAfdelingID, description, 0, CAST (AS AfdelingID VARBINARY (900))
 Afdeling FROM Department
 ParentAfdelingID IS NULL WHERE IS NULL ParentAfdelingID

 UNION ALL

 SELECT 
 , a. ParentAfdelingID , a. Omschrijving , b. AfdelingsLevel +1 , a. AfdelingID, ParentAfdelingID a., a. Description, b. Division Level 1, 
  ( b. SortKey + CAST ( a. AfdelingID AS BINARY ( 4 ) ) AS VARBINARY ( 900 ) ) CAST (CAST + b. SortKey (a. AfdelingID AS BINARY (4)) AS VARBINARY (900))
 Afdeling AS a FROM Department AS a
  AfdelingTree AS B ON a. ParentAfdelingID = b. AfdelingID INNER JOIN Department AS B ON a. Tree ParentAfdelingID AfdelingID = b.
 ) 

The CTE has been named Tree Division in which a number of columns defined.
Subsequently, the SELECT statement to build the first section to define where the ParentAfdelingID NULL. This is the department defines the top of the chart and the highest level. It become a sortkey defined subsequently applied for an ORDER BY.

In the second SELECT statement we look at the child-sections that contain ParentAfdelingID the previous query. This is done by a JOIN in the CTE in the second statement, the self-reference. This part of the query is repeated until no more records remain, in short to all departments are handled and the parent-child chart is drawn. Besides, the sortkey added to the sortkey of the parent in order to get a leveling.

Now the rest is built CTE only a SELECT statement issued to the chart in the CTE to retrieve.

  SELECT
  ( '-->    ' , AfdelingsLevel ) + '(' + ( CAST ( AfdelingID AS VARCHAR ( 10 ) ) ) + ') ' + Omschrijving AS AfdelingOmschrijving, Replicate ('->', Section Level) + '(' + (CAST (AS VARCHAR AfdelingID (1910))) + ')' + Description AS Description Department,
  AfdelingID
  ParentAfdelingID,
  Description,
  Division Level,
  SortKey
 AfdelingTree FROM Department Tree
 SortKey ORDER BY SortKey 

Using the Replicate function can be indented by department level so as to chart the hierarchy within SQL Server visible. The output of the query has the following result due to:

Tags See All Tags

CTE Common Table Expression SQL