I was planning to do a series of blogs about the new PIVOT and UNPIVOT functionality in SQL Server 2005, but I’ve decided to take a step back and do a post on Common Table Expressions (CTE’s) first. They are used in conjunction with PIVOT and UNPIVOT to make the code a bit cleaner.
If you have used derived tables in your scripts before than Common Table Expressions will easy for you to understand.
Here is a simple derived table example (from the Adventure Works sample DB) that returns the total quantity of items for each order in the month of December 2001:
SELECT SalesOrderID,
NumberOfItems
FROM(
Sum(sod.OrderQty) as NumberOfItems
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE OrderDate >= ’12/1/2001′
AND OrderDate < ’1/1/2002′
GROUP BY soh.SalesOrderID
) AS TotalQty
And here is the same query, done as a CTE. Notice the use of the WITH keyword:
WITH TotalQty
AS (
SUM(sod.OrderQty) as NumberOfItems
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE OrderDate >= ’12/1/2001′
AND OrderDate < ’1/1/2002′
GROUP BY soh.SalesOrderID
)
SELECT SalesOrderID,
NumberOfItems
FROM TotalQty
