title: SQL Server pivot on 2 (or multiple) aggregates
body: <p>Is it possible to pivot on multiple aggregated columns in SQL Server? I have the following order table: </p>
  
  <pre><code>[ORDERS]
  | CustName | OrderedProduct | QtyOrdered | UnitCost | UnitPrice |  OrderDate
    Bob      | Canned Tuna    |          6 |       11 |        14 | 21-12-2016 13:11:00
    Steve    | Canned Salmon  |          2 |       15 |        19 | 03-11-2016 11:03:00
    Bob      | Canned Tuna    |         10 |       10 |        13 | 22-12-2016 10:43:00
    Bob      | Canned Tuna    |          4 |        9 |        10 | 13-11-2016 17:22:00
  </code></pre>
  
  <p>I have the following pivot query which gives me an order quantity per product per customer for months Nov/Dec:</p>
  
  <pre><code>SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016] FROM
  (
      SELECT CustName, OrderedProduct, QtyOrdered,
      'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
      FROM [ORDERS]
  ) src
  PIVOT
  (
      SUM(QtyOrdered)
      FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
  ) pvtvol
  WHERE CustName='Bob'
  </code></pre>
  
  <p>This gives the, expected, result</p>
  
  <pre><code>CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016
  Bob      | Canned Tuna    |           16 |            4
  </code></pre>
  
  <p>What I want is to also pivot on margin so changing the <strong>src</strong> query to:</p>
  
  <pre><code>    SELECT CustName, OrderedProduct, QtyOrdered,
      ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
      'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
      'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
      FROM [ORDERS]
  </code></pre>
  
  <p>I have tried the following modified query adding a second pivot</p>
  
  <pre><code>SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016], [MAR Dec 2016], [MAR Nov 2016] FROM
  (
      SELECT CustName, OrderedProduct, QtyOrdered,
      ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
      'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
      'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
      FROM [ORDERS]
  ) src
  PIVOT
  (
      SUM(QtyOrdered)
      FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
  ) pvtvol
  PIVOT
  (
      SUM(Margin)
      FOR YearMonthMar IN ([MAR Dec 2016], [MAR Nov 2016])
  ) pvtmar
  WHERE CustName='Bob'
  </code></pre>
  
  <p>The table I would <strong>EXPECT</strong> to see is </p>
  
  <pre><code>CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
  Bob      | Canned Tuna    |           16 |            4 |           48 |            4
  </code></pre>
  
  <p>The table I <strong>actually</strong> see is</p>
  
  <pre><code>CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
  Bob      | Canned Tuna    |           16 |         NULL |           48 |         NULL
  Bob      | Canned Tuna    |         NULL |            4 |         NULL |            4
  </code></pre>
  
  <p>So, the pivot on margin does not seem to be applied, and I get a row for each order. Is what I'm trying to achieve actually possible with the method I'm trying to use, or would I be better to use a crosstab query for VOL and MAR or a mixture of pivot and crosstab (pivot for VOL, crosstab for MAR?)</p>
  
  <p>Any advice greatly appreciated.</p>