SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


SQL Server Recursive Query with Recursive CTE (Common Table Expression)

SQL Server Recursive Query structure is first introduced with SQL Server 2005 Common Table Expression improvement. CTE (Common Table Expression) has a wide usage area in T-SQL programming. But what makes CTE indispensable in SQL development is its recursive query creation features.

An SQL Server recursive query is formed of three sections.

WITH RecursiveCTEQuery AS (
  {Anchor Query}
  UNION ALL
  {Query joined to RecursiveCTEQuery}
)
SELECT * FROM RecursiveCTEQuery
Code

The anchor query within the recursive CTE query is the first section. Anchor query is the start up row for recursion. For example, in the anchor query you select the top level item. Or you select a specific row using the WHERE clause in the anchor query. Anchor query is the first row in the first recursion of the CTE expression.

Second part in SQL recursive CTE expression is a SELECT statement from the target table. This is generally the same table used in anchor SELECT. But this time it is INNER JOIN 'ed with the recursive CTE. The INNER JOIN condition identifies whether you are going to upper levels or you're querying to lower levels. This INNER JOIN expression set the parent/child relation between rows in the main sql table.

The result sets of the CTE inner sections are combined into a single return set using with UNION ALL expression

The last section is the SELECT statement which query CTE itself.

Assume that the company we are going to use in our SQL Server recursive query samples is named Adventure Works Cycle. And assume that hierarchical organizational structure of Adventure Works Cycle company is as follows.

As usual there are parent and child organizational units in the chart representing a hierarchy structure. These are parent/child rows in our database table where we will soon design to store the company hierarchical structure.

SQL Server recursive query sample structure

The OrganizationalStructures table is very simple in design. It has a self-reference column ParentUnitID which refers to BusinessUnitID field of the one level upper organizational unit.

Right after the CREATE TABLE sql command, sample data for the above organizational chart is populated using SQL INSERT INTO command. We will use this sql table and data in it for SQL Server recursive query samples in this T-SQL tutorial.

Create Table OrganizationalStructures (
 BusinessUnitID smallint identity(1,1),
 BusinessUnit varchar(100) Not Null,
 ParentUnitID smallint
)
insert into OrganizationalStructures values
('Adventure Works Cycle',NULL),
('Customer Care',1),
('Service',1),
('Channel Sales & Marketing',1),
('Customer Support',2),
('OEM Support',2),
('Central Region',3),
('Eastern Region',3),
('Western Region',3),
('OEM',4),
('Channel Marketing',4),
('National Accounts',4),
('Channel Field Sales',4),
('National Channel Marketing',11),
('Retail Channel Marketing',11),
('Central Region',13),
('Eastern Region',13),
('Western Region',13),
('Bicycles',15),
('Bicycle Parts',15)

Code

Let's see what is our sample data looks like using SQL SELECT command.

select * from OrganizationalStructures
Code

In this SQL tutorial using Recursive CTE (Common Table Expression), sql programmers will soon be able to query hierarchical sql data and return a list of business units in related with each other with parent/child properties

SQL Recursive query sample database table

The following CTE - Common Table Expression is a sample of SQL Server Recursive Query.
The below SQL recursive query returns a list of rows from OrganizationalStructures which has BusinessUnitID equal to 1 and the sub-items of this anchor row.

WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = 1

 UNION ALL

 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE
Code

Our first SQL Server Recursive CTE query returns all records in the table since the anchor select returns the top level item in the organization chart hierarchy.

SQL Server Recursive CTE query on Adventureworks

Of course, it is important to build reusable code in SQL just like other programming languages. TSQL developers can save the above SQL Server recursive query in a stored procedure by making simple modifications in the CTE expression in order to make it parametric.

We can change the anchor SELECT part of the inner CTE expression to alter the recursive query. As you see in the following t-sql code, the anchor query returns rows with BusinessUnitID equals to @BusinessUnitID stored procedure parameter value. And actually this is the all change required for creating parameteric recursive queries.

CREATE PROCEDURE sp_ListOrganizationalStructuresOf (
 @BusinessUnitID smallint
)
AS
WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = @BusinessUnitID

 UNION ALL

 SELECT
  child.BusinessUnitID,
  child.BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE
GO
EXEC sp_ListOrganizationalStructuresOf 11
Code

Right after the stored procedure is created, I executed the SP to return business units defined below Channel Marketing (direct or indirectly combined)

recursive CTE query sample in SQL Server

Let's modify the above SQL Server recursive query in order to add some detail information about the hierarchy and to give some visual effects as follows

WITH Recursive_CTE AS (
 SELECT
  child.BusinessUnitID,
  CAST(child.BusinessUnit as varchar(100)) BusinessUnit,
  CAST(child.ParentUnitID as SmallInt) ParentUnitID,
  CAST(NULL as varchar(100)) ParentUnit,
  CAST('>> ' as varchar(100)) LVL,
  CAST(child.BusinessUnitID as varchar(100)) Hierarchy,
  1 AS RecursionLevel
 FROM OrganizationalStructures child
 WHERE BusinessUnitID = 1

 UNION ALL

 SELECT
  child.BusinessUnitID,
  CAST(LVL + child.BusinessUnit as varchar(100)) AS BusinessUnit,
  child.ParentUnitID,
  parent.BusinessUnit ParentUnit,
  CAST('>> ' + LVL as varchar(100)) AS LVL,
  CAST(Hierarchy + ':' + CAST(child.BusinessUnitID as varchar(100)) as varchar(100)) Hierarchy,
  RecursionLevel + 1 AS RecursionLevel
 FROM Recursive_CTE parent
 INNER JOIN OrganizationalStructures child ON child.ParentUnitID = parent.BusinessUnitID
)
SELECT * FROM Recursive_CTE ORDER BY Hierarchy
Code

The output of the above SQL Recursive CTE query will result as follows

SQL Server recursion with CTE recursive query

I hope SQL developers will enjoy the SQL Server recursive query structure just as I do. For more SQL Server CTE (Common Table Expression) features, you can refer to T-SQL tutorial New Features in SQL Server 2005 - Common Table Expression (SQL CTE)



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.