DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to form Common Table Expression (CTE)?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE