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

*/

------------

Related articles:

Common Table Expressions (CTE) in SQL Server 2005

SQL Server CTE Basics