|
Common Table
Expression (CTE) is an alternative for using temporary tables, table variables
or views.
CTE 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 following it.
Here are 2 examples for Common Table Expression usage:
USE AdventureWorks;
GO
-- Crosstab query with pivot - SQL common table expression - SQL cte
WITH cteVendorPO
AS (SELECT PurchaseOrderID,
Employee = FirstName + ' ' + LastName,
Vendor = v.Name
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID)
SELECT *
FROM cteVendorPO v
PIVOT
(Count(PurchaseOrderID)
FOR Employee IN ( [Erin Hagens],[Linda Meisner],[Sheela Word],
[Frank Pellow],[Reinout Hillmann] ) ) AS pvt
ORDER BY pvt.Vendor
GO
/* Partial results
Vendor Erin Hagens Linda Meisner Sheela Word
Advanced Bicycles 5 5 2
Allenson Cycles 4 5 2
American Bicycles and Wheels 4 5 2
American Bikes 4 5 1
Anderson's Custom Bikes 5 6 2
Aurora Bike Center 4 5 3
Australia Bike Retailer 4 5 2
*/
-- Get purchasing employee list for PIVOT query above
-- All or a subset of employees can be used for the PIVOT
-- SQL common table expression - CTE
WITH ctePurchaseStaff AS
(
SELECT DISTINCT
Employee = FirstName + ' ' + LastName
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
INNER JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID
)
SELECT * FROM ctePurchaseStaff
ORDER BY Employee
GO
/* Results
Employee
Annette Hill
Arvind Rao
Ben Miller
Eric Kurjan
Erin Hagens
Frank Pellow
Fukiko Ogisu
Gordon Hee
Linda Meisner
Mikael Sandberg
Reinout Hillmann
Sheela Word
*/
|
