Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com .
Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.
USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO
In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.
Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result). This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.
This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.
Reference : Pinal Dave (https://blog.sqlauthority.com), Special thanks to Jr. Developers at my organization who helped me to prepare example.
95 Comments. Leave new
Very Useful
Thanks @Durga
Thanks, nice post
I have a table inwhich a employee and their supervisor details are present, i need to update a column the same table with number of level distance from owner of the company(with a perticualr higest employee id). Would you plz advice me to with optimize query for the same.
Thanks in advance
You are awesome. You always seem to have the answers to my SQL problems / questions
Help me here…
i need to get all column from a table which has columns id, name,department,date….but instead of date i want two new columns maxdate,mindate…considering 2 or more different dates can be there against same name……
Hi Rahul, Look if your query gets resolved by the below solution.
create table #tbl3(Id INT, Name VARCHAR(50), Department VARCHAR(50),DateTm DATETIME)
INSERT INTO #tbl3 VALUES
(1, ‘A’, ‘AA’, GETDATE()-1),
(1, ‘A’, ‘AA’, GETDATE()),
(2, ‘B’, ‘BB’, GETDATE()),
(2, ‘B’, ‘BB’, GETDATE()-2),
(3, ‘C’, ‘CC’, GETDATE()-3),
(3, ‘C’, ‘CC’, GETDATE()),
(4, ‘D’, ‘DD’, GETDATE()),
(4, ‘D’, ‘DD’, GETDATE()-4)
SELECT * FROM #tbl3
SELECT ID, Name, Department,
min(DateTm),
max(DateTm)
from #tbl3
group by ID, Name, Department
I need a query for calculating Each Component value in salary structure, each component defined by a formula, one component is set as input component which i pass as parameter,
Ex- CTC is input Component, Formula for Basic is CTC*0.3, DA is Basic*0.5, HRA is Basic+DA
Formula can anything given by user while creating structure, now i am facing difficulty in calculating Component Values based on Formula…
I think it can achieved by CTE forming a tree structure using inputcomponent as root…..
Can you help me out for writing query…..
Thanks in advance
–Kartik Reddy
Declare @temp Table(EMPID INT)
INSERT @temp values(0)
INSERT @temp values(1)
INSERT @temp values(0)
INSERT @temp values(1)
select ABS(EMPID-1) from @temp
Declare @temp Table(EMPID INT)
INSERT @temp values(0)
INSERT @temp values(1)
INSERT @temp values(0)
INSERT @temp values(1)
select ABS(EMPID-1) from @temp
Reply
Hi Mr. Pinal Dave,
I am newbie to hierarchy CTE and I am very much like to learn on this topic and I hope you can guide me. Thanks
The following is a query result which interested me most how it end up in a very proper hierarchy level, how tables are being used?
My understanding is that, Account tables must have had 5 tables that related by parent and child (1,2,3,4,5 level).
AccountParent, AccountChild1, AccountChild2, AccountChild3, AccountChild4 and AccountChild5.
AccountChild5 relate to Account Parent through AccountChild4 and AccountChild4 to AccountChild3 and so on.
I Hope you can guide by providing a concrete example to follow.
Many thanks.
Level Account Balance
———– —————————————— ———————
1 Revenue 2300,00
2 Direct Income 300,00
3 Sales 300,00
4 Sales 300,00
5 In Store Sales 100,00
5 Online Sales 200,00
2 Indirect Income 2000,00
3 Interest 1100,00
4 Bank Interest 1100,00
5 Bank Interest A 400,00
5 Bank Interest B 700,00
3 Other Income 900,00
4 Other Income 900,00
5 Other Income 900,00
Hi, I have the following records
slno ProductCode ProductDesc Qty grnNo Price
1 RAM002 DDR4 4GB LAPTOP RAM 2 1617-000060 1925.37
2 RAM002 DDR4 4GB LAPTOP RAM 2 1617-000061 1925.37
3 RAM002 DDR4 4GB LAPTOP RAM 4 1617-000130 2268.25
4 RAM002 DDR4 4GB LAPTOP RAM 6 1617-000187 2321.00
5 RAM002 DDR4 4GB LAPTOP RAM 10 1617-000197 2241.8999
6 RAM002 DDR4 4GB LAPTOP RAM 4 1718-000013 2162.75
I need to fetch only those rows which are satisfying my condition regarding Qty aggregation suppose if I give Qty as 5 as a factor, the rows, with slno 1,2,3 should be displayed since this falls in the criteria i,e, sum as 8, the remaining records should not be fetched
similarly if I provide 22 as my Qty , slno 1,2,3,4,5 should display excluding slno 6…
if the the qty provided with 40 then all the rows should display…
Hi Dave, I need to check if a record descends from another regardless the levels in between, for which I guessed a recursive CTE would come with the solution, but I can only manage to list direct children from each parent. Is there a way to list AscendentId, DescendentId from hierarchical data ChildId, ParentId?