Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using a CTE with a T-SQL Pivot Statement

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.

 

 

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.