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
Like Oracle, is it possible to get ROWID in SQL Server…
If so how?
Here is my parser without loop::
CREATE PROCEDURE [dbo].[usp_parser]
@string NVARCHAR(MAX)
AS
SELECT @string = ””+REPLACE(@string,’/’,”’ ))AS ROWS UNION ALL SELECT RTRIM(LTRIM(”’)+””
SELECT @string = ‘SELECT RTRIM(LTRIM( ‘ + @string + ‘)) AS ROWS’
EXEC SP_EXECUTESQL @string
Hi Pinal, i have a good one, i still can´t solve it and hope you can help me:
This is my table:
idConcept idParentConcept Description
1 NULL Root Concept
2 NULL Root Concept
3 NULL Root Concept
4 1 Child Level 1
5 1 Child Level 1
6 4 Child Level 2
7 2 Child Level 1
8 2 Child Level 1
So if i use a recursive CTE i get this resultset after executing it
idParentConcept idConcept Descripcion Level
NULL 1 Root Concept 0
NULL 2 Root Concept 0
NULL 3 Root Concept 0
1 4 Root Concept 1
1 5 Root Concept 1
2 7 Root Concept 1
2 8 Root Concept 1
4 6 Root Concept 2
I´m going to use this to generate a static web menu, so the records must be ordered as a tree:
Root Node 1
Child Node 4
Child Node 5
Child Node 6<- with the order by solution don´t work
Root Node 2
Child Node 7
Child Node 8
I can solve this with an ORDER BY, and it shows well with jus 2 levels, but when add a 3rd level this fails, shows the 3rd level at the end of the result set.
Hope you can help me Pinal.
Thanks for share your knowledge ;)
i also have same problem. i too need the solution for same problem. using order by it is successful til 2nd level. please provide me the solution for same.
Try this:
DECLARE @Temp TABLE (idConcept INT, idParentConcept INT, [Description] VARCHAR(50));
INSERT INTO @Temp VALUES (1, NULL, ‘Root Concept’);
INSERT INTO @Temp VALUES (2, NULL, ‘Root Concept’);
INSERT INTO @Temp VALUES (3, NULL, ‘Root Concept’);
INSERT INTO @Temp VALUES (4, 1, ‘Child Level 1’);
INSERT INTO @Temp VALUES (5, 1, ‘Child Level 1’);
INSERT INTO @Temp VALUES (6, 4, ‘Child Level 2’);
INSERT INTO @Temp VALUES (7, 2, ‘Child Level 1’);
INSERT INTO @Temp VALUES (8, 2, ‘Child Level 1’);
WITH cte AS (
SELECT
idConcept,
idParentConcept,
[Description]
FROM
@Temp
WHERE
idParentConcept IS NULL
UNION ALL
SELECT
t.idConcept,
t.idParentConcept,
t.[Description]
FROM
@Temp t
INNER JOIN cte ON t.idParentConcept = cte.idConcept
)
SELECT
c1.*
FROM
cte c1
LEFT JOIN cte c2 ON c2.idConcept = c1.idParentConcept
LEFT JOIN cte c3 ON c3.idConcept = c2.idParentConcept
ORDER BY
COALESCE(c3.idConcept, c2.idConcept, c1.idConcept),
COALESCE(c2.idConcept, c1.idConcept),
c1.idConcept;
–idConcept idParentConcept Description
–1 NULL Root Concept
–4 1 Child Level 1
–5 1 Child Level 1
–6 4 Child Level 2
–2 NULL Root Concept
–7 2 Child Level 1
–8 2 Child Level 1
–3 NULL Root Concept
Then I thought you probably want the child with the id = 6 to come after its parent in the list, i.e. 1, 4, 6, 5, 2, 7, 8, 3.
This will work for three levels (same table variable as before):
WITH cte AS (
SELECT
idConcept,
idParentConcept,
NULL AS ParentParent,
[Description]
FROM
@Temp
WHERE
idParentConcept IS NULL
UNION ALL
SELECT
t.idConcept,
t.idParentConcept,
cte.idParentConcept,
t.[Description]
FROM
@Temp t
INNER JOIN cte ON t.idParentConcept = cte.idConcept)
SELECT
idConcept,
idParentConcept,
[Description]
FROM
cte
ORDER BY
COALESCE(ParentParent, idParentConcept, idConcept),
CASE WHEN ParentParent IS NULL THEN idConcept ELSE idParentConcept END,
idConcept;
CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine. Follow the link to know the details…
h ttp://www.sqllion.com/2010/08/common-table-expressions-cte/
Thank you so much.. It was really helpful
Excellent example. Thanks.
Really useful information. Really helped me a lot to understand the concept of CTEs
Please forgive me for my ignorance, but I would like to know what is HumanResources in the above example. I know that Employee is the table and AdventureWorks is the database. Any help to a beginer would be appreciated.
Thanks for the link,
THe resulting dataset is
2 Fuller NULL
1 Davolio 2
3 Leverling 2
4 Peacock 2
5 Buchanan 2
8 Callahan 2
6 Suyama 5
7 King 5
9 Dodsworth 5
would it be possible to query the managers for
9 Dodsworth 5?
e.g. it would return
2 Fuller Null
5 Buchanan 2
because Buchanan is his direct superier, and Fuller is Buchanans superior?
Hi all,
I am having a temptable with 100 names in it, and I want to execute a SP with all the 100 names as the parameters of the temptable and the output of the SP I want to store in another temp table say(XtempTable).
Can I achieve this using CTE. if yes..
Please advice how can I go with that using CTE.
Thanks
Manish
Thank you so much, It is very useful.
Sorry for my english,
How can I use CTE in SSAS ,creating a named query on a DSV?
is there a way to avoid a loop (nocycle for those of you who know oracle plsql) using cte?
Hello Pinal,
My Table Contains Such Type of Record
ID Name ParentID
1 Item 1 NULL
2 Item 2 NULL
3 Cate 1 1
4 Cate 2 2
5 Cate 3 1
6 Cate 4 1
7 Cate 5 1
8 Cate 6 2
…… so on …. may be there is possibility of child of Cate 1, Cate 2….
Actually using CTE, It will Give me Output Like this :
ID Name ParentID
1 Item 1 NULL
2 Item 2 NULL
4 Cate 2 2
8 Cate 6 2
3 Cate 1 1
5 Cate 3 1
6 Cate 4 1
7 Cate 5 1
But I want OutPut Like this :
ID Name ParentID
1 Item 1 NULL
3 Cate 1 1
5 Cate 3 1
6 Cate 4 1
7 Cate 5 1
2 Item 2 NULL
4 Cate 2 2
8 Cate 6 2
How can I get Such Type of Output ??…. Please Help me …
DECLARE @table table (ID int, Name varchar(20),ParentID int, row int)
INSERT INTO @table
SELECT 1,’Item 1′, NULL, 0 UNION ALL
SELECT 2,’Item 2′, NULL, 0 UNION ALL
SELECT 3,’Cate 1′, 1, 0 UNION ALL
SELECT 4,’Cate 2′, 2, 0 UNION ALL
SELECT 5,’Cate 3′, 1, 0 UNION ALL
SELECT 6,’Cate 4′, 1, 0 UNION ALL
SELECT 7,’Cate 5′, 1, 0 UNION ALL
SELECT 8,’Cate 6′, 2, 0
UPDATE @table SET row = CASE WHEN ParentID IS null then ID ELSE ParentID end
;with Parents as (
SELECT ID,Name,ParentID,row from @table where ParentID IS null
UNION all
SELECT [@table].ID,[@table].Name,[@table].ParentID ,[@table].row
FROM @table inner join Parents on [@table].ParentID = Parents.ID
)
SELECT ID,Name,ParentID from Parents ORDER BY row,ID
The CTE works well, but doesn’t give the order I need. The problem I have is that I want to order the records as follows:
CEO
Bob – Reports to CEO
Joe – Reports to Bob
Mike – Reports to Joe
May – Reports to Bob
Andy – Reports to CEO
Tom – Reports to Andy
The spaces are for clarity
This is the code I am using at the moment:
WITH GroupReports (GroupID, ReportsTo, GroupName, MemberID, Level)
AS
(
— Anchor member definition
SELECT e.GroupID, e.ReportsTo, e.GroupName, e.MemberID,
0 AS Level
FROM dbo.Groups AS e
WHERE e.ReportsTo IS NULL AND e.MemberID = @GroupID
UNION ALL
— Recursive member definition
SELECT e.GroupID, e.ReportsTo, e.GroupName, e.MemberID,
Level + 1
FROM Groups e
INNER JOIN GroupReports d
–ON e.GroupID = d.ReportsTo
–ON e.ReportsTo = d.ReportsTo
ON e.ReportsTo = d.GroupID
)
— Statement that executes the CTE
SELECT gr.GroupID, gr.ReportsTo, gr.GroupName, gr.MemberID, Level
FROM GroupReports gr
And this is how it returns the rows:
CEO
Bob – Reports to CEO
Andy – Reports to CEO
May – Reports to Bob
Joe – Reports to Bob
Tom – Reports to Andy
Mike – Reports to Joe
It looks like all level one are selected, then all level two’s and so on. That is not what I want. I need to have them grouped by ‘Reports To’.
Any help on this one would be much appreciated.
Hi,
I have a table with below data
Column A Column B
1 101
1 102
2 103
2 104
3 105
3 106
I need a result set as follows
101, 103, 105
101, 103, 106
101, 104, 105
101, 104, 106
102, 103, 105
102, 103, 106
102, 104, 105
102, 104, 106
Can you please help me how to get the above result using CTE
Hi Gautam,
have you got answer for this question….?
Please , help me out, if you got answer
I have a transaction table: Tran
Table Structure: Applid, SalesAmount, EmpId
I have an employee table: Employee
Table Structure: EmpId, EmpRole, ManagerId
Employee table can have 4 emp roles: A, B, C, D (D is the highest role, A is the lowest role)
The employee Id in Tran table (EmpId) can have employees with role A, B, C or D. Send me a query which would show the sum of SalesAmount for the employees at role D.
Tran Table:
Applid SalesAmount EmpID
1 1000 1001
2 2000 1002
3 3000 1003
4 4000 1004
5 5000 1005
6 6000 1006
7 7000 1007
8 8000 1008
9 9000 1009
10 10000 1001
Employee:
EmpID Role ManagerId
1001 A 1002
1002 B 1003
1003 C 1005
1004 A 1002
1005 D —-
1006 A 1007
1007 B 1010
1008 B 1010
1010 C 1009
1009 D —-
Desired output:
EmployeeID TotalSalesAmount
1005 25000
1009 30000
R u able to resolve this?
Very usefull thanks..
Please help me in this.. i have written cte but facing problem in achieving my desired output:
organizationtable:
oid, orgname, level, parentid
10-unit1-1-0
11-dept1-2-10
12-dept2-2-10
13-sec1-3-11
empdtls:
eid staffno oid
1-99-13
cte which i have writted is below:
with testorg as(
select oid,orgname,parentid from #organizationdtls where parentid = 0
union all
select a.oid,a.orgname,a.parentid from #organizationdtls a inner join testorg t on(a.parentid = t.oid)
)
select t.* from testorg t
Help me in achieving such output if i pass oid = 10 then it should return
empdtls:
eid staffno oid
1-99-13
Please help me !!