How does Recursive CTE works in Sql server?

Welcome to Appsloveworld Technologies, in the Session, learn about recursive common table expressions, before continuing with this post, I strongly recommend you to read the below rticle of CTE.

• What is CTE in sql server with example
• How to update table using CTE in sql server

I have this TblEmployeeManager table which has got EmpId, EmployeeName, and Manager_Id columns.

Since a manager is also an employee in that organization. We store both employee and manager details in the same table. Now if I ask you who is the manager of Michael, you will look up the Manager_Id column, which is number 5.

You will then take that value and look up in the empID column. Number 5 is Grayson. So Michael’s manager is Grayson.

we are taking the Manager_Id column and looking up another column in the same table. So you are referencing the same table. So it’s called a self-referencing table.

Now, if I ask you to display employee names along with their manager’s name, then we have to join this table with itself. And we know that joining a table with itself is called a self join.

we have talk about self join extensively in What is SELF JOIN ? with Realtime Example post.

So this is a simple self-join query obviously if I just want to display the employee names along with their manager names, we can make use of a simple self join.

Select Emp.EmployeeName as [Employee Name],
IsNull(Mng.EmployeeName, 'Super Boss') as [Manager Name]
from TblEmployeeManager Emp
left join TblEmployeeManager Mng
on Emp.Manager_Id = Mng.EmpId

if you look at Anthony Manager_Id is NULL, which means Anthony does not have any manager, so we are displaying “Super Manager” with the help of the IsNull function.

So if you just want to display employee name along with their manager’s name, you can simply use  self-join.

Sql server hierarchical query example

But let’s say I want to display the employee name, their manager name, and along with that, I also want to display the level in the organization.

Now if you look at this organization chart below, Anthony is the super manager because he doesn’t have a manager_Id. Which means he’s at the top of the hierarchy. and then to Anthony, there are two direct reports.

Lincoln and Ryan, are the direct reports of Anthony. 

if you look at Anthony, his employee Id=2, and Lincoln and Ryan, their manager_id is 2, So obviously, Lincoln and Ryan report to Anthony, and then Lincoln in a similar fashion has got one direct report.

Ryan also got one. So if you look at the hierarchy, Anthony, is that at the highest level and Jackson,Michael ,Luke and Jacob are at the bottom of the hierarchy.

So if you look at the levels of these employees, Anthony level is one, he’s at the top. And if you look at Lincoln and Ryan, they are level two.

you can see employees, Lincoln and Ryan, their level 2 along the same lines, Gabriel and Grayson has level three and at the bottom of the hierarchy, Jackson, Jacob, Michael, Luke with level four.

So right now, I want to display employee name, their manager name, and their level within the organization. Now, to achieve this, we can use recursive.

Using recursive CTE the code will become a lot easier.

With
  EmployeesManagerCTE (EmpId, Name, ManagerId, [Level])
  as
  (
    Select EmpId, EmployeeName, Manager_Id, 1
    from TblEmployeeManager
    where Manager_Id is null    
    union all   
    Select TblEmployeeManager.EmpId, TblEmployeeManager.EmployeeName, 
    TblEmployeeManager.Manager_Id, EmployeesManagerCTE.[Level] + 1
    from TblEmployeeManager
    join EmployeesManagerCTE
    on TblEmployeeManager.Manager_Id = EmployeesManagerCTE.EmpId
  )
Select EmpCTE.Name as Employee, Isnull(MgrCTE.Name, 'Super Boss') as Manager, 
EmpCTE.[Level] 
from EmployeesManagerCTE EmpCTE
left join EmployeesManagerCTE MgrCTE
on EmpCTE.ManagerId = MgrCTE.EmpId

we know that we create a CTE using the “WITH” keyword. So with EmployeesManagerCTE.

So here EmployeesManagerCTE is a CTE and then this is obviously having four columns, employeeId, name, Manager_Id, and level columns.

The reason why I have put level and square brackets are because the level is a keyword in SQL Server

if you want to use a keyword as a column name, then you will have to wrap that inside the square brackets.

if you look at the CTE query within the “AS” block, we are again, referencing the CTE within the CTE. So this kind of becomes a self-referencing CTE.

Table Script

/****** Object:  Table [dbo].[TblEmployeeManager]    Script Date: 09/22/2020 7:21:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO

The post How does Recursive CTE works in Sql server? appeared first on Software Development | Programming Tutorials.