|
Common Table
Expression (CTE) is an alternative for using temporary tables
or views. It is a temporary table result set whose scope is limited
to the execution of a SELECT, INSERT, UPDATE, DELETE or
CREATE VIEW statement. CTE is a virtual table which can be referenced
several times in the associated query. It can also be self-referenced hence recursive. Here are two examples:
USE AdventureWorks;
WITH cteSupervisor(ManagerID, StaffCount)
AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
GROUP BY ManagerID
)
SELECT Manager=LEFT(FirstName,1)+'. ' +LastName,
e.Title, StaffCount
FROM cteSupervisor s
JOIN HumanResources.Employee e
ON s.ManagerID = e.EmployeeID
JOIN Person.Contact c
ON c.ContactID = e.ContactID
ORDER BY LastName
GO
/*
Manager Title StaffCount
S. Abbas Pacific Sales Manager 1
H. Abolrous Quality Assurance Manager 2
P. Ackerman Shipping and Receiving Supervisor 5
A. Alberts European Sales Manager 3
*/
-- AdventureWorks Cycles company organization chart
-- Recursive Common Table Expression - Recursive CTE
WITH cteOrgChart
(LastName, FirstName, Title, ManagerID, EmployeeID, HierarchyLevel)
AS
(
-- Anchor part
SELECT
LastName,
FirstName,
e.Title,
ManagerID,
EmployeeID,
1 as HierarchyLevel
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE ManagerID IS NULL -- CEO
UNION ALL
-- Recursive part
SELECT
c.LastName,
c.FirstName,
e.Title,
e.ManagerID,
e.EmployeeID,
oc.HierarchyLevel + 1 AS HierarchyLevel
FROM HumanResources.Employee e
INNER JOIN cteOrgChart oc
ON e.ManagerID = oc.EmployeeID
INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
)
SELECT Name = FirstName+' '+LastName,
Title,
MgrID = ManagerID,
EmpID = EmployeeID,
HierarchyLvl = HierarchyLevel
FROM cteOrgChart
ORDER BY HierarchyLevel, LastName, FirstName
/* Partial results
Name Title MgrID EmpID HierarchyLvl
Ken Sánchez Chief Executive Officer NULL 109 1
David Bradley Marketing Manager 109 6 2
Terri Duffy Vice President of Engineering 109 12 2
James Hamilton Vice President of Production 109 148 2
Laura Norman Chief Financial Officer 109 140 2
Jean Trenary Information Services Manager 109 42 2
Brian Welcker Vice President of Sales 109 273 2
Syed Abbas Pacific Sales Manager 273 288 3
*/
|
