title: Multi-Column, Multi-Row PIVOT
body: <p>Consider that I have a table which contains data in the following form:</p>
  
  <pre><code>Foo_FK  MonthCode_FK  Activity_FK  SumResultsX  SumResultsY
  -----------------------------------------------------------
  1       201312        0            10           2
  1       201312        1            5            1
  1       201401        0            15           3
  1       201401        1            7            2
  2       201312        0            9            3
  2       201312        1            1            2
  2       201401        0            6            2
  2       201401        1            17           4
  </code></pre>
  
  <p>For my purposes, it is safe to assume that this table is an aggregation which would have been created by a <code>GROUP BY</code> on <code>Foo_FK, MonthCode_FK, Activity_FK</code> with <code>SUM( ResultsA ), SUM( ResultsB )</code> to obtain the data, making <code>Foo_FK, MonthCode_FK, Activity_FK</code> unique per record.</p>
  
  <p>If for some reason I found it preferable to <code>PIVOT</code> this table in a stored procedure to ease the amount of screwing around with SSRS I'd have to do ( and undoubtedly later maintain ), wishing to get the following format for consumption via a matrix tablix thingy:</p>
  
  <pre><code>Foo_FK  1312_0_X  1312_0_Y  1312_1_X  1312_1_Y  1401_0_X  1401_0_Y  1401_1_X  1401_1_Y  
  --------------------------------------------------------------------------------------
  1       10        2         5         1         15        3         7         2
  2       9         3         1         2         6         2         17        4
  </code></pre>
  
  <p>How would I go about doing this in a not-mental way?  Please refer to this <a href="http://sqlfiddle.com/#!3/e76c7/16" rel="nofollow noreferrer">SQL Fiddle</a> at proof I am likely trying to use a hammer to build a device that pushes in nails.  Don't worry about a dynamic version as I'm sure I can figure that out once I'm guided through the static solution for this test case.</p>
  
  <p>Right now, I've tried to create a <code>Foo_FK, MonthCode_FK</code> set via the following, which I then attempt to <code>PIVOT</code> ( <a href="http://sqlfiddle.com/#!3/e76c7/16" rel="nofollow noreferrer">see the Fiddle for the full mess</a> ):</p>
  
  <pre><code>SELECT  Foo_FK = ISNULL( a0.Foo_FK, a1.Foo_FK ),
          MonthCode_FK = ISNULL( a0.MonthCode_FK, a1.MonthCode_FK ),
          [0_X] = ISNULL( a0.SumResultX, 0 ),
          [0_Y] = ISNULL( a0.SumResultY, 0 ),
          [1_X] = ISNULL( a1.SumResultX, 0 ),
          [1_Y] = ISNULL( a1.SumResultY, 0 )
  FROM (  SELECT  Foo_FK, MonthCode_FK, Activity_FK, 
                  SumResultX, SumResultY
          FROM    dbo.t_FooActivityByMonth
          WHERE   Activity_FK = 0 ) a0
  FULL OUTER JOIN (
          SELECT  Foo_FK, MonthCode_FK, Activity_FK, 
                  SumResultX, SumResultY
          FROM    dbo.t_FooActivityByMonth
          WHERE   Activity_FK = 1 ) a1
      ON  a0.Foo_FK = a1.Foo_FK;
  </code></pre>
  
  <p>I have come across some <a href="https://stackoverflow.com/a/21257388/1382359">excellent advice on this SO question</a>, so I'm in the process of performing some form of <code>UNPIVOT</code> before I twist everything back out using <code>PIVOT</code> and <code>MAX</code>, but if there's a better way to do this, I'm all ears.</p>