Pivot Example using a CTE

use AdventureWorks2008
GO
-- First we'll retrieve Orders from 2003 and place into a CTE
WITH OrdCTE AS (
SELECT VendorID, Name As VendorName,  DatePart(m,OrderDate) AS OrderMonth, POHdr.TotalDue
FROM Purchasing.PurchaseOrderHeader POHdr
join Purchasing.Vendor ON Vendor.BusinessEntityID = POHdr.VendorID
where YEAR(OrderDate) = 2003
)   
-- Now we'll query the CTE, and use a PIVOT statement           
SELECT VendorID, VendorName, [1] AS Jan,[2] AS Feb,[3] AS Mar,[4] AS Apr, 
 [5] AS May, [6] AS June, [7] AS July, [8] AS Aug, 
 [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM OrdCTE
-- we take each of the 12 possible values and associate them with 12 buckets
PIVOT (SUM(TotalDue) FOR OrderMonth IN ([1],[2],[3],[4],
 [5], [6], [7], [8],           
 [9], [10], [11], [12]))  as TempList