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.
]]>