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
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.
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)
Let's see what is our sample data looks like using SQL SELECT command.
select * from OrganizationalStructures
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
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
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.
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
Right after the stored procedure is created, I executed the SP to return business units defined below Channel Marketing (direct or indirectly combined)
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
The output of the above SQL Recursive CTE query will result as follows
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)