Figure 14 Setting up for UNPIVOT
　

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 *
INTO SalesPivoted
FROM OrdersCTE
  PIVOT(SUM(OrderValue) FOR OrderYear
    IN([2001], [2002], [2003], [2004])) AS P