More About Common Table Expressions in SQL Server 2005 - A recursive common table expression using views: example
(Page 5 of 5 )
Till now we have simple examples. Let us start extending the above example to include both employee names and manager names by creating a recursive CTE using views.
create view vEmpManagerDetails
as
WITH EmployeeManagersDetails(EmployeeID, EmployeeName, ManagerID,
Manager)
AS (SELECT a.EmployeeID, a.Name, a.ManagerID,b.Name AS
Manager
FROM (SELECT HumanResources.Employee.EmployeeID,
Person.Contact.FirstName + ' ' +
Person.Contact.MiddleName
+ ' ' + Person.Contact.LastName AS Name,
HumanResources.Employee.ManagerID
FROM Person.Contact INNER JOIN
HumanResources.Employee ON
Person.Contact.ContactID =
HumanResources.Employee.ContactID) AS a LEFT OUTER JOIN
(SELECT HumanResources.Employee.EmployeeID,
Person.Contact.FirstName + ' ' +
Person.Contact.MiddleName
+ ' ' + Person.Contact.LastName AS Name,
HumanResources.Employee.ManagerID
FROM Person.Contact INNER JOIN
HumanResources.Employee ON
Person.Contact.ContactID =
HumanResources.Employee.ContactID) AS b
ON a.ManagerID = b.EmployeeID)
SELECT EmployeeID, EmployeeName, ManagerID, Manager
FROM EmployeeManagersDetails
The above script creates the view “VEmpManagerDetails” using a non-recursive CTE. This view displays managers and the employees reporting to them.
Now let us create a recursive CTE based on the above view:
WITH EmployeeManagerDetails(ManagerID,Manager,
EmployeeID,EmployeeName, EmployeeLevel) AS
(
SELECT ManagerID,Manager,EmployeeID, EmployeeName, 0 AS
EmployeeLevel
FROM dbo.vEmpManagerDetails
WHERE ManagerID IS NULL
UNION ALL
SELECT vem.ManagerID,vem.Manager,
vem.EmployeeID,vem.EmployeeName, EmployeeLevel + 1
FROM dbo.vEmpManagerDetails vem
INNER JOIN EmployeeManagerDetails d
ON vem.ManagerID = d.EmployeeID
)
SELECT ManagerID, Manager,EmployeeID,EmployeeName, EmployeeLevel
+1 as EmployeeLevel
FROM EmployeemanagerDetails order by EmployeeLevel;
GO
I hope everybody can understand the above script. It is similar to the previous example. The only difference we can find is that it displays the Names of Employees and Managers along with their IDs and levels. I also added “EmployeeLevel + 1” just to eliminate the “0” in the output.
The sample output for the above CTE would be something like the following:
ManagerID Manager EmployeeID EmployeeName EmployeeLevel
-------- --------- ---------- ------------ -------------
NULL NULL 109 Ken J Sánchez 1
109 Ken J.. 6 David M Bradley 2
109 Ken J.. 12 Terri Lee Duffy 2
.
.
.
273 Brian.. 268 Stephen Y Jiang 3
273 Brian.. 284 Amy E Alberts 3
273 Brian.. 288 Syed E Abbas 3
.
.
.
30 Paula M.. 47 Willis T Johnson 4
30 Paula M.. 70 Mindy C Martin 4
30 Paula M.. 82 Vidur X Luthra 4
30 Paula M.. 154 Hao O Chen 4
.
.
.
210 Brenda.. 31 Alejandro E McGuel 5
210 Brenda.. 45 Fred T Northup 5
210 Brenda.. 56 Kevin H Liu 5
210 Brenda.. 68 Shammi G Mohamed 5
.
.
.
Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
