http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=RecursiveCTE&referringTitle=Home
A Common Table Expression(CTE) is an ANSI SQL-99 temporary result set that can be declared once and referenced multiple times in a query. It acts as a replacement for many sub-queries and thereby improves query simplicity and performance in most cases.
If you are new to CTE’s, I would recommend you to check this link http://msdn.microsoft.com/en-us/library/ms190766.aspx before you proceed ahead with Recursive CTE’s.
A recursive CTE is one in which a CTE refers to itself in the CTE definition to obtain subsets of data until the complete result set is obtained. Recursive CTE’s come in quiet handy when you need to represent hierarchical data and query against it. Infact, the raw power of a CTE is realized when it is used recursively to obtain a resultset. In previous versions of SQL Server, you could create recursive queries using cursors and temporary tables.
The syntax of a recursive query would be similar to the following:
WITH cte-nm (column1, column2, …)
AS
(
cte-query-definition — Anchor member
UNION ALL
cte-query-definition –- Recursive member which references cte_name
)
SELECT * FROM cte-nm
In this sample, we will see how to use Recursive CTE’s to find out the level of a team member in a project.
Case Scenario : Let us assume that we have a table called ProjectMemberDetails. We need to display the ‘Level’ of each TeamMember in that project. The level of each team member will be ascertained by the hierarchy of Managers above the person.
For the sake of demonstrating Recursive CTE’s, we will create the following columns in the ProjectMemberDetails table – ID, TeamMemNm, Designation and ManagerID. Using Recursive CTE’s, we will then calculate the level of each team member in the team. Level 1 is the highest grade, given only to those who do not have managers above them; in our case the EngagementManager.
The hierarchy of a project team for demonstratation purposes is as follows:
EngagementManager – 1
Sr. Project Manager – 2
Project Manager – 3
Project Leader – 4
Team Leader – 5
Sr. Developer – 6
Developer/Tester – 7
The code to display the hierarchy would be as follows:
SQL Server 2005/SQL Server 2008
DROP TABLE #ProjectMemberDetails
-- create temporary table called ProjectMemberDetails
CREATE TABLE #ProjectMemberDetails
(
ID int Identity(1,1) PRIMARY KEY,
TeamMemNm varchar(100),
Designation varchar(50),
ManagerID int NULL
)
-- Insert some sample records in the ProjectMemberDetails table
INSERT INTO #ProjectMemberDetails VALUES('Tim','Engagement Manager',NULL);
INSERT INTO #ProjectMemberDetails VALUES('Kathy','Sr. Project Manager',1);
INSERT INTO #ProjectMemberDetails VALUES('Levonca','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Sid','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Carrie','Project Leader',3);
INSERT INTO #ProjectMemberDetails VALUES('Cristina','Project Leader',4);
INSERT INTO #ProjectMemberDetails VALUES('Santi','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Michelle','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Pablo','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Mario','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Anand','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Bill','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Jack','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Tibre','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Lisa','Sr. Developer',9);
INSERT INTO #ProjectMemberDetails VALUES('Scott','Sr. Tester',10);
INSERT INTO #ProjectMemberDetails VALUES('Nancy','Developer',11);
INSERT INTO #ProjectMemberDetails VALUES('MJ','Developer',12);
INSERT INTO #ProjectMemberDetails VALUES('Ziak','Developer',13);
INSERT INTO #ProjectMemberDetails VALUES('Martin','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('David','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('Steve','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Rachael','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Sally','Tester',16);
INSERT INTO #ProjectMemberDetails VALUES('Peter','Tester',16);
SELECT * FROM #ProjectMemberDetails;
-- Use Recursive CTE to find out the Level of each ProjectMemberDetails
-- in the project
WITH CTE(ID, TeamMemberName, Designation, ManagerID, ProjectLevel)
AS
(
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
UNION ALL
SELECT e.ID, e.TeamMemNm, e.Designation, e.ManagerID, c.ProjectLevel + 1
FROM #ProjectMemberDetails e
INNER JOIN CTE c
ON e.ManagerID = c.ID
)
SELECT ID, TeamMemberName, Designation, ManagerID, ProjectLevel
FROM CTE Order BY ID
Here’s a break up of the query:
1. In the query below, the anchor member returns the person who is at the highest level in the project (Engagement Manager) and marks the level as 1
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
2. To determine the person directly below him (EngagementManager), a join is made between the ProjectMemberDetails table and the CTE, which helps in determining the member which is directly below the person returned by the anchor member. In our case, that would be the Project Manager.
3. With every recursive invocation, we get the output (c.ProjectLevel + 1) for the join e.ManagerID = c.ID
The final result set consists of the union of all the subsets generated by the anchor and recursive members. The result set is displayed below.
Result
| ID |
TeamMemberName |
Designation |
ManagerID |
ProjectLevel |
| 1 |
Tim |
Engagement Manager |
NULL |
1 |
| 2 |
Kathy |
Sr. Project Manager |
1 |
2 |
| 3 |
Levonca |
Project Manager |
2 |
3 |
| 4 |
Sid |
Project Manager |
2 |
3 |
| 5 |
Carrie |
Project Leader |
3 |
4 |
| 6 |
Cristina |
Project Leader |
4 |
4 |
| 7 |
Santi |
Team Leader |
5 |
5 |
| 8 |
Michelle |
Team Leader |
5 |
5 |
| 9 |
Pablo |
Team Leader |
6 |
5 |
| 10 |
Mario |
Team Leader |
6 |
5 |
| 11 |
Anand |
Sr. Developer |
7 |
6 |
| 12 |
Bill |
Sr. Developer |
7 |
6 |
| 13 |
Jack |
Sr. Developer |
8 |
6 |
| 14 |
Tibre |
Sr. Developer |
8 |
6 |
| 15 |
Lisa |
Sr. Developer |
9 |
6 |
| 16 |
Scott |
Sr. Tester |
10 |
6 |
| 17 |
Nancy |
Developer |
11 |
7 |
| 18 |
MJ |
Developer |
12 |
7 |
| 19 |
Ziak |
Developer |
13 |
7 |
| 20 |
Martin |
Developer |
14 |
7 |
| 21 |
David |
Developer |
14 |
7 |
| 22 |
Steve |
Developer |
15 |
7 |
| 23 |
Rachael |
Developer |
15 |
7 |
| 24 |
Sally |
Tester |
16 |
7 |
| 25 |
Peter |
Tester |
16 |
7 |
References: http://msdn.microsoft.com/en-us/library/ms186243.aspx
