Recursive CTE in SQL Server Simplified

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:

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

-- 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!