Click here to Skip to main content
16,017,881 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

Recursive CTE in SQL Server Simplified

Rate me:
Please Sign up or sign in to vote.
4.50/5 (3 votes)
7 Feb 2015CPOL 11.3K   10  
This tip is a brief introduction to Recursive CTE in SQL Server and how to implement it.

Introduction

In this tip, I am going to explain Recursive CTE in a very simplified way.

I hope you all know what is a CTE. A recursive CTE is basically a CTE that has reference to itself. This tip basically puts light on Recursive CTE and shows you how it is useful in to create an organization (like IT company) hieararical tree.

So let's explain what I mean by Recursive CTE with SQL code and explain with an example.

Background

There are various scenarios where we want to show up hierarchical data (organization chart, org chart, or simply a tree).

Using the Code

I hope you have an idea of self join before I explain further how to implement a Recursive CTE. The steps are there in the code section below:

SQL
Create Table Emp
(
  EmployeeId int Primary key,
  Name nvarchar(20),
  ManagerId int
)

Insert into Emp values (1, 'Tommy', 2)
Insert into Emp values (2, 'Gaurav', null)
Insert into Emp values (3, 'Micheal', 2)
Insert into Emp values (4, 'Johny', 3)
Insert into Emp values (5, 'Pam', 1)
Insert into Emp values (6, 'Marie', 3)
Insert into Emp values (7, 'James', 1)
Insert into Emp values (8, 'Alicia', 5)
Insert into Emp values (9, 'Simon', 1)

-- get EmployeeName , ManagerName as column headers by simple self join :

Select employee.Name as [EmployeeName],Coalesce(manager.Name,'Founder') as [ManagerName] 
from Emp employee
Left Join Emp manager
ON employee.ManagerId= manager.EmployeeID

SQL
-- get EmployeeName , ManagerName, Hierarical level as column headers  
--  ( here comes the recursive CTE)

;WITH empCTEwithLevelheader (EmployeeID,Name,ManagerID,[Level])
AS
(
  -- Top level employee or 'Founder' Row - hardcoded hierarchy level 1
SELECT EmployeeId,Name,ManagerId,1 FROM Emp WHERE ManagerId IS NULL 
UNION ALL
-- All Employee Row below to top level ( Note that this query have JOIN with CTE itself)
-- and increasing the level on each loopsies
Select employee.EmployeeId,employee.Name,employee.ManagerId,empCTEwithLevelheader.[Level] + 1 
from Emp employee
Join empCTEwithLevelheader
ON employee.ManagerId= empCTEwithLevelheader.EmployeeID
)

-- get ManagerName column from above CTE result-set, finally by Self join  

Select Employee.Name as [EmployeeName],ISNULL(manager.Name,'Founder') as [ManagerName],
Employee.[Level] from empCTEwithLevelheader employee
Left Join empCTEwithLevelheader manager
ON employee.ManagerId= manager.EmployeeID

Running the above CTE will get the desired output:

Thanks! That's it - I hope you will find it useful. Attached is the code snippet!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
-- There are no messages in this forum --