title: SQL Server PIVOT with multiple X-axis columns
body: <p>Take the following example data:</p>
  
  <pre><code>Payroll Forname Surname Month   Year    Amount
  0000001 James   Bond    3       2011    144.00
  0000001 James   Bond    6       2012    672.00
  0000001 James   Bond    7       2012    240.00
  0000001 James   Bond    8       2012    1744.50
  0000002 Elvis   Presley 3       2011    1491.00
  0000002 Elvis   Presley 6       2012    189.00
  0000002 Elvis   Presley 7       2012    1816.50
  0000002 Elvis   Presley 8       2012    1383.00
  </code></pre>
  
  <p>How would i PIVOT this on the Year + Month (eg: 201210) but preserve Payroll, Forename &amp; Surname as seperate columns, for example, the above would become:</p>
  
  <pre><code>Payroll Forename    Surname 201103  201206  201207  201208
  0000001 James       Bond    144.00  672.00  240.00  1744.50
  0000002 Elvis       Presley 1491.00 189.00  1816.50 1383.00
  </code></pre>
  
  <p>I'm assuming that because the Year + Month names can change then i will need to employ dynamic SQL + PIVOT - i had a go but couldnt even get the code to parse, nevermind run - any help would be most appreciated!</p>
  
  <p>Edit: What i have so far:</p>
  
  <pre><code>    INSERT  INTO #tbl_RawDateBuffer
              ( PayrollNumber ,
                Surname ,
                Forename ,
                [Month] ,
                [Year] ,
                AmountPayable
              )
              SELECT  PayrollNumber ,
                      Surname ,
                      Forename ,
                      [Month] ,
                      [Year] ,
                      AmountPayable
              FROM    RawData
              WHERE   [Max] &gt; 1500
  
  
  DECLARE @Columns AS NVARCHAR(MAX)
  DECLARE @StrSQL AS NVARCHAR(MAX) 
  
  SET @Columns = STUFF((SELECT DISTINCT
                                  ',' + QUOTENAME(CONVERT(VARCHAR(4), c.[Year]) + RIGHT('00' + CONVERT(VARCHAR(2), c.[Month]), 2))
                        FROM      #tbl_RawDateBuffer c
      FOR              XML PATH('') ,
                           TYPE 
              ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
  
  SET @StrSQL = 'SELECT PayrollNumber, ' + @Columns + ' from 
              (
                  select PayrollNumber
                      , CONVERT(VARCHAR(4), [Year]) + RIGHT(''00'' + CONVERT(VARCHAR(2), [Month]), 2) dt
                  from #tbl_RawDateBuffer
             ) x
              pivot 
              (
                  sum(AmountPayable)
                  for dt in (' + @Columns + ')
              ) p '
  
  
  EXECUTE(@StrSQL)
  
  DROP TABLE #tbl_RawDateBuffer
  </code></pre>