Common Table Expressions (CTE) for SQL Server Developers

Replacement for Sub queries

Sub queries can cause issues as they reduce code readability. In the above example we eliminated sub queries when using CTEs.

There are frequent questions on the forums of how to write a single T-SQL statement with multiple CTEs. I wrote a blog post on this.

Here is how you can use two CTEs to join together. In the following example, sales for 2007 and 2008 are combined.

WITH Prod2007 (ProductNumber,Amount)
AS
(
     SELECT Prod.ProductNumber,SUM(LineTotal)
     FROM Sales.SalesOrderDetail SOD INNER JOIN
     Sales.SalesOrderHeader SOH
     ON SOD.SalesOrderID = SOH.SalesOrderID
     INNER JOIN Production.Product Prod
     ON Prod.ProductID = SOD.ProductID
     WHERE YEAR(OrderDate) = 2007
     GROUP BY Prod.ProductNumber
)
,
Prod2008 (ProductNumber,Amount)
AS
(
     SELECT Prod.ProductNumber,SUM(LineTotal)
     FROM Sales.SalesOrderDetail SOD INNER JOIN
     Sales.SalesOrderHeader SOH
     ON SOD.SalesOrderID = SOH.SalesOrderID
     INNER JOIN Production.Product Prod
     ON Prod.ProductID = SOD.ProductID
     WHERE YEAR(OrderDate) = 2008
     GROUP BY Prod.ProductNumber
)
SELECT Prod2007.ProductNumber,
Prod2007.Amount Sales2007,
Prod2008.Amount Sales2008 ,
Prod2008.Amount - Prod2007.Amount SalesIncrement
FROM Prod2007
INNER JOIN   Prod2008
ON Prod2007.ProductNumber = Prod2008.ProductNumber

Below is the result.

Ranking by Aggregates

If you are using ranking functions for aggregates, you have no other options other than to use sub-queries. However, with CTEs you can use ranking functions easily.

WITH CustomerSum
AS
(
SELECT CustomerID,SUM(TotalDue) AS TotalAmount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)
SELECT *,RANK() OVER (ORDER BY TotalAmount DESC ) AS OrderRank
FROM CustomerSum

Dynamic Pivot Tables

Pivot tables often neglected as its columns cannot be dynamic. However, with CTEs you can still support dynamic columns with pivot tables.

Below is the traditional Pivot table usage.

-- Pivot
SELECT *
FROM
(SELECT CustomerID, YEAR(OrderDate)AS OrderYear, TotalDue
FROM Sales.SalesOrderHeader) header
PIVOT
(SUM(TotalDue) FOR OrderYear IN ([2006],[2007],[2008])
) AS pvt

This is how you can use this with CTE.

DECLARE @years VARCHAR(1000)
WITH Years
AS
(SELECT Distinct Year(OrderDate) AS Year
FROM Sales.SalesOrderHeader)
SELECT @years = ISNULL(@years+ ',[','[')+ CAST([Year] AS NVARCHAR(10))+']'
FROM Years
ORDER BY Year
DECLARE @sql  nvarchar(max)
SET @sql =
'SELECT *
FROM
(SELECT CustomerID, YEAR(OrderDate)AS OrderYear, TotalDue
FROM Sales.SalesOrderHeader) header
PIVOT
(SUM(TotalDue) FOR OrderYear IN ('+   @years+ ')
) AS pvt
'
exec sp_executesql @sql

All the scripts are available in sky drive for you to download.