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
Thanks Pinal.
I still didn’t the solution for this.. Plz help.
Table has following 2 columns
DocNum DocEntry
1 234
2 324
2 746
3 876
3 764
4 100
4 387
Expected result is as follow
1 234
2 324, 746
3 876, 764
4 100, 387
create table #tbl1(DocNum TINYINT, DocEntry INT)
INSERT INTO #tbl1 VALUES
(1, 234),
(2, 324),
(2, 746),
(3, 876),
(3, 764),
(4, 100),
(4, 387)
SELECT * FROM #tbl1
select DocNum,STUFF((SELECT ‘,’ + cast(DocEntry as varchar)
from #tbl1 #tbl11
where #tbl11.DocNum = #tbl1.DocNum
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)
from #tbl1
group by DocNum
Hi, can you please explain me why in this example in the recursive member stands p.PerAssemblyQty and not bom.PerAssemblyQty?
WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
ComponentLevel
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO
Thank you in advance
Hello All
I have two tables like below. This is something like parent child relationship. Table 1 have relation details and Table 2 have ids for each row.
Table 1
Col1 Col2 col3
A A1 A1.1
B B1 B1.1
B B1 B1.2
C C1 C1.1
C C2 C2.1
C C3 C3.1
Table 2
Col1 KEY
A 1
A1 2
A1.1 3
B 4
B1 5
B1.1 6
B1.2 7
C 8
C1 9
C1.1 10
C2 11
C2.1 12
C3 13
C3.1 14
Extecpted output
CHILD PARENT
14 13
12 11
10 9
13 8
11 8
9 8
7 5
6 5
5 4
3 2
2 1
NULL 1
how to get it using quyer
CREATE TABLE [dbo].[Jogo](
[Jogo] [float] NULL,
[Casa] [float] NULL,
[Coluna1] [nvarchar](3) NULL,
[Coluna2] [nvarchar](3) NULL,
[Coluna3] [nvarchar](3) NULL
) ON [PRIMARY]
GO
Jogo,Casa,Coluna1,Coluna2,Coluna3
255,1,C1:,NULL,NULL
255,2,A1:,B1:,C1:
255,3,A1:,NULL,NULL
255,4,A1:,C1:,NULL
255,5,A1:,NULL,NULL
255,6,C1:,B1:,NULL
255,7,A1:,C1:,NULL
255,8,A1:,NULL,NULL
255,9,A1:,NULL,NULL
255,10,A1:,B1:,C1:
255,11,A1:,NULL,NULL
255,12,C1:,NULL,NULL
255,13,A1:,NULL,NULL
255,14,A1:,C1:,NULL
255,15,A1:,NULL,NULL
Jogo Casa Coluna1 Coluna2 Coluna3
255 1 C1: NULL NULL
255 2 A1: B1: C1:
255 3 A1: NULL NULL
255 4 A1: C1: NULL
255 5 A1: NULL NULL
255 6 C1: B1: NULL
255 7 A1: C1: NULL
255 8 A1: NULL NULL
255 9 A1: NULL NULL
255 10 A1: B1: C1:
255 11 A1: NULL NULL
255 12 C1: NULL NULL
255 13 A1: NULL NULL
255 14 A1: C1: NULL
255 15 A1: NULL NULL
Using SQL generate all possible between the columns 1, 2 and 3.
This is an example of practical utilization of query recurssive applied to sports lottery (loteca).
The result should be 144 bet as element 15 in the column should be based on CASA.
FIRST LINE == B1:A1:A1:A1:A1:B1:A1:A1:A1:A1:A1:B1:A1:A1:A1:
LAST LINE == B1:B1:A1:B1:A1:CA1::B1:A1:A1:B1:A1:B1:A1:B1:A1:
Good article…
Hello,
I have a requirment where the hierarchy structure is defined in the below way.
QA Sponsor
QA Fund 1
QA Fund 2
QA Fund 3
QA Fund 4
QA Fund 5
QA Fund 1
Below is the proposed db design.
Child Id Level Parent
QA Sponsor 1
QA Fund1 2 QA Sponsor
QA Fund 2 3 QA Fund1
QA Fund 3 4 QA Fund 2
QA Fund 4 5 QA Fund 3
QA Fund 5 2 QA Sponsor
QA Fund1 2 QA Fund 5
I have written the below CTE code and which provide me the o/p which is mentioned after the code.
;WITH cte AS
(
SELECT CAST(” + Name AS VARCHAR(100)) as ‘Name’, ID
FROM dbo.RELATION
WHERE PARENT_ID =”
UNION ALL
SELECT CAST(cte.Name + ‘—>’ + t.Name AS VARCHAR(100)), t.ID
FROM dbo.RELATION t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT Name FROM cte
ORDER BY ID
O/P
—
QA SPONSOR
QA SPONSOR—>QA FUND1
QA SPONSOR—>QA FUND1—>QA FUND2
QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3
QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4
QA SPONSOR—>QA FUND5
QA SPONSOR—>QA FUND5—>QA FUND1
Everything looks ok however but since QA Fund 1 has fund 2,3,and 4 inside it. there should be one more line i.e. QA SPONSOR—>QA FUND5—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4
I am stuck and not sure what should i do to get around this problem. Please help me in this.
I found query but it use 3 select query but to improve performance is there any way so i can retrieve answer less than 3 select query? Either by with cte or rownumber or rank or partition by?
hi pinal can u please revert back with a query to find a palindrome of a string using CTE recursive function
thanks in advance. .
really need it
Hi Pinal,
I need to get userdetails from mytable who havent done any transactions in last
N Months . transaction details and userdetails are maintained in different tables.
Can You tell me the query How to find out what are the child tables suppose if I have given table Name
Example Suppose If I have given table name like emp
I need to find out what are the child tables for employeeclass
I am trying to extract numeric values from a field (NodePath) that are seperated by decimal points. For example, .1.2.56.4235.8571. each numeric value represents a unique identifyer (NodeId) for a folder or file in an application. The db I’m pulling the data from (objectstore..Lar_Tree) has the NodeId (int), the NodePath (varchar(900)) and the NodeName (varchar(128)). Once I have the numeric values extracted I want to concatenate them into a path for documentation. Someone suggested using a recursive query, but I have not written one yet and can’t seem to get it to work based on the example above. Any help would be greatly appreciated.
…btw…This is how I was approching it prior to hearing I should use a recursive query.
PRINT’List all Wiz DBs; Lar_Names, DB_Name, Types, CensusYear’
SELECT
NodeName,
NodeId,
LEN(NodePath) – LEN(REPLACE(NodePath, ‘.’, ”)) AS DCount,
NodePath,
Left(objectstore..LAR_Tree.NodeName,40)AS Lar_Name,
Left(objectstore..LAR_Items.DatabaseName,10) AS Database_Name,
objectstore..LAR_Items.LARType, objectstore..LAR_Items.CensusYear
INTO #tbl_Temp
FROM objectstore..LAR_Tree
Left JOIN objectstore..LAR_Items
ON objectstore..LAR_Tree.NodeId = objectstore..LAR_Items.ItemId
ORDER BY Database_Name
SELECT
NodePath,
CensusYear,
Database_Name,
(CASE
WHEN DCount = 3 THEN (CASE
WHEN LEN(NodePath) = 5 THEN
(SELECT NodeName
FROM objectstore..LAR_Tree
WHERE NodeId = CAST(SUBSTRING(NodePath,4,1)AS INT))
WHEN LEN(NodePath) = 6 THEN
(SELECT NodeName
FROM objectstore..LAR_Tree
WHERE NodeId = CAST(SUBSTRING(NodePath,4,2)AS INT))
WHEN LEN(NodePath) = 7 THEN
(SELECT NodeName
FROM objectstore..LAR_Tree
WHERE NodeId = CAST(SUBSTRING(NodePath,4,3)AS INT))
WHEN LEN(NodePath) = 8 THEN
(SELECT NodeName
FROM objectstore..LAR_Tree
WHERE NodeId = CAST(SUBSTRING(NodePath,4,4)AS INT))
END)
END) AS GUI_Path
FROM #tbl_Temp
Great Pinal!
You are god of sql
i have a table called test :- create table test (id int , Docname varchar(200), parentkey int, ContentType varchar(200))
insert into test values (1,’ParentFolder’,-3,’Folder’)
insert into test values (2,’ChildFolder1′,1,’Folder’)
insert into test values ( 3,’ChildFolder1′,2,’Folder’)
insert into test values (4,’ParentFolder 2′,-3,’Folder’)
insert into test values (5,’test Folder 1′,4,’Folder’)
insert into test values (6,’test Folder 2′,4,’Folder’)
in this table id is the primarykey, i have a column called parentkey in this table, in this table records having parent-child relation
ex:- id Name PrentKey
1 test -3
2 test2 1
-3 is the parentkey here, 1 vlaue in the parentkey is the child of id 1,
here if i give child id key i want to get the all names of that child n parent fileds. example :- if i give ‘2’ as input parament i want out put like below :
ID Path
1-2 test-test2
for this i m trying below query :-
WITH Emp_CTE ( id,ParentKey,ContentType,Path,Level)
AS (
SELECT id , ParentKey,ContentType,
CONVERT(varchar(1000),DocName) , 0 Level
FROM test S
WHERE id = 2
UNION ALL
SELECT SS.ID , SS.ParentKey,SS.ContentType,
CONVERT(varchar(1000), ISNULL(SS.DocName,”)+’|’+ISNULL(Path,”) )
,Level +1
FROM test SS
INNER JOIN Emp_CTE ecte ON ecte.ParentKey = SS.ID
)
SELECT CASE WHEN Path IS NULL THEN ” ELSE ‘|’ END + Path as folderpath,
* FROM Emp_CTE WHERE ContentType = ‘Folder’ AND ParentKey = -3
ablove query is giving output as below :-
ID Path
1 test|test2
here i want to display all the IDs , but i am able to append all the docnames from table , how to achive id’s like 1-2?
Hello,
I have to use table valued function twice in a same SELECT statement. So will l SQL retrieve data once or twice if the same function is used in two places?
If it retrieves twice will CET resolves it?
Thank you!
Hi Pinal,
the above example can be done even with joins and groupby. how about the below be more apt example for cte?
with CI AS (SELECT t.name,t.object_id,count(t.object_id) CI
FROM sys.indexes i inner join sys.tables t
on i.object_id = t.object_id
where t.type like ‘u’ and i.type_desc like ‘CLUSTERED’
group by t.name,t.object_id)
, NCI AS (SELECT t.name,t.object_id,count(t.object_id) NCI
FROM sys.indexes i inner join sys.tables t
on i.object_id = t.object_id
where t.type like ‘u’ and i.type_desc like ‘NONCLUSTERED’
group by t.name,t.object_id)
SELECT t.name,CI.CI,NCI.NCI,CI.CI + NCI.NCI TI
FROM sys.tables t inner join CI on CI.Object_id = t.object_id
inner join NCI on NCI.object_id = t.object_id
where t.type like ‘u’
order by TI desc
select t.name,count(i.index_id)
from sys.tables t inner join sys.indexes i on i.object_id = t.object_id
where i.type_desc in (‘CLUSTERED’,’NONCLUSTERED’)
group by t.name
order by 2 desc
Here is my idea of Simple.
WITH cte AS
(
SELECT 1 as n
UNION ALL
SELECT n + 1
FROM cte
WHERE n + 1 <= 10
)
SELECT n
FROM cte
I think this helps people understand it a bit better.
easy to understand
Thanks @karthiga
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