Figure 12 PIVOT on CTE
　

WITH OrdersCTE(SalesPersonID, OrderYear, OrderValue)
AS
(
  SELECT
    SOH.SalesPersonID, 
    YEAR(SOH.OrderDate),
    SOD.OrderQty * SOD.UnitPrice
  FROM SalesOrderHeader AS SOH
    JOIN SalesOrderDetail AS SOD
      ON SOD.SalesOrderID = SOH.SalesOrderID
)
SELECT *
FROM OrdersCTE
  PIVOT(SUM(OrderValue) 
    FOR OrderYear IN([2001], [2002], [2003], [2004])) AS P