During a T-SQL class that I was recently teaching I was asked if a PIVOT could be performed using a Common Table Expression (CTE). In other words, instead of using the method outlined in SQL Server Books Online that uses a derived table, replace the derived table with a CTE. I have never attempted this approach, but I was confident that it could be done. Using the AdventureWorks database I initially wrote a T-SQL PIVOT statement that uses a derived table, which is as follows:
|
USE AdventureWorks GO SELECT * FROM ( SELECT st.CountryRegionCode, TotalDue, YEAR(OrderDate) OrderYear FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID ) p PIVOT ( SUM(p.TotalDue) FOR OrderYear IN ([2001],[2002],[2003],[2004]) ) AS pvt |
This is a pretty straight-forward pivot statement. As with any derived table to CTE conversion, instead of using the query as the source of the derived table, use it as the query definition for the CTE. See the following example:
|
USE AdventureWorks GO ;WITH p AS ( SELECT st.CountryRegionCode, TotalDue, YEAR(OrderDate) OrderYear FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID ) SELECT * FROM p PIVOT ( SUM(p.TotalDue) FOR OrderYear IN ([2001],[2002],[2003],[2004]) ) AS pvt;
|
As with any T-SQL statement I am sure that there are several variations that are available to accomplish the same task. In my opinion the CTE makes the T-SQL syntax more readable. If you have any questions or comments please email me at pleblanc@pragmaticworks.com.
Talk to you soon,
Patrick LeBlanc
Founder www.TSQLScripts.com and www.SQLLunch.com.
Visit www.BIDN.com, Bring Business Intelligence to your company.
