Search This Blog

Recursive CTE in SQL with Example

Recursive CTE in SQL with Example

A recursive CTE is defined by at least two queries (more are possible)—at least one query known as the anchor member and at least one query known as the recursive member.
The two queries are joined using Union all where the anchor member will run only once and recursive member will run repeatedly until it will returns empty result set.

Recursive CTE mostly used when returning a hierarchical list of employees, starting with the highest ranking employee in a company.

Below is the Example of Recursive CTE.


-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


if you see above table the table has Empname Empid with there Manager ID

Case I to display Manager Name of all the Employee with there the Title the Top Manager is Ken is the CEO of company who doesn't have any manager


--Below is the query displaying Employee and there Managers Names

select m.FirstName,m.Title ,m.EmployeeID,e.ManagerID,E.FirstName as mgrname,E.Title as MgtTitle  from MyEmployees E right  join MyEmployees M
on  m.managerid= e.EmployeeID 

Output

FirstName Title EmployeeID ManagerID mgrname MgtTitle
Ken Chief Executive Officer 1 NULL NULL NULL
David Marketing Manager 16 1 Brian Vice President of Sales
Mary Marketing Specialist 23 273 David Marketing Manager
Brian Vice President of Sales 273 NULL Ken Chief Executive Officer
Stephen North American Sales Manager 274 1 Brian Vice President of Sales
Michael Sales Representative 275 273 Stephen North American Sales Manager
Linda Sales Representative 276 273 Stephen North American Sales Manager
Syed Pacific Sales Manager 285 1 Brian Vice President of Sales
Lynn Sales Representative 286 273 Syed Pacific Sales Manager

Case II -Now we want to show Hierarchy of employees, starting with the highest ranking employee which is Ken then Brain along with there level 

So here we will use Recursive CTE.


If you see the above screenshot the Employee are in Hierachy along with with there manager Name

So below is the Recursive CTE to achieve Hierarchy of Employee.

with cte (firstname,title,EmployeeID,ManagerID,lvl)
as
(
--anchor member returning head of company
select firstname,title,EmployeeID,ManagerID,0 as lvl from MyEmployees where ManagerID is null
union all
--recursive member (will do recursion until reached to last level)
select e.firstname,e.Title,e.EmployeeID,e.ManagerID, lvl+1 from MyEmployees e   join cte on 
cte.EmployeeID =e .ManagerID )
select e.FirstName as ManagerName, M.* from cte M left join MyEmployees E 
on m.ManagerID =e.EmployeeID  

Output

ManagerName firstname title EmployeeID ManagerID lvl
NULL Ken Chief Executive Officer 1 NULL 0
Ken Brian Vice President of Sales 273 1 1
Brian David Marketing Manager 16 273 2
Brian Stephen North American Sales Manager 274 273 2
Brian Syed Pacific Sales Manager 285 273 2
Syed Lynn Sales Representative 286 285 3
Stephen Michael Sales Representative 275 274 3
Stephen Linda Sales Representative 276 274 3
David Mary Marketing Specialist 23 16 3

NOTE:By default SQL Server restricts the number of times that the recursive member can be invoked to 100. The code will fail upon the 101st invocation of the recursive member. You can change the default maximum recursion limit by specifying the hint OPTION(MAXRECURSION n) at the end of the outer query, where n is an integer.





No comments:

Post a Comment