SQLUSA

Microsoft SQL Server 2005 Best Practices

 

How to form Common Table Expression (CTE)?

 

Common Table Expression (CTE) is an alternative for using temporary tables, table variables 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 following it. Here is an example where CTE is applied in conjunction with a PIVOT:

 

USE AdventureWorks;
GO
WITH cteVendorPO
AS
(
SELECT PurchaseOrderID,
Employee=FirstName+' '+LastName,
Vendor = v.Name
FROM Purchasing.PurchaseOrderHeader poh
JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
JOIN Person.Contact c
ON e.ContactID = c.ContactID
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


 

The World Leader in SQL Server Training
 
SQLUSA.com Home Page