title: Pivot table with dynamic columns in date order
body: <p>I have a pivot table creating 2 columns for each line that I would like to go in date order.</p>
  
  <pre><code>Below is the data in the raw format
  -------------------------------------------------------------
  partnum   | period  | TotalQty     |     ToldSold
  
  005483-6  | 2015-08 | 100.00000000 |    389.379000
  
  0551105   | 2015-08 | 10.00000000  |    4560.773000
  
  0CT202305 | 2015-09 | 4.00000000   |    2285.430800
  
  0CTR00905 | 2015-10 | 2.00000000   |    654.305400
  
  183386-32 | 2016-01 | 20.00000000  |    75.060400
  
  24-175UV50| 2016-03 | 450.00000000 |    42.723000
  </code></pre>
  
  <p>I have the following code to generate the dynamic pivot table</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
  @colsName AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(period +'_'+c.col)
                    from #orderhistory
                    cross apply 
                    (
                      select 'TotalQty' col
                      union all
                      select 'ToldSold'
                    ) c
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)')
      ,1,1,'')
  
  select @cols
  
  
  
  select @colsName 
  = STUFF((SELECT distinct ', ' + QUOTENAME(period +'_'+c.col) 
             +' as ['
             + period + case when c.col = 'TotalQty' then ' QtySold]' else 'Total $ Sold]' end
           from #orderhistory
           cross apply 
           (
              select 'TotalQty' col
              union all
              select 'ToldSold'
           ) c
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
      ,1,1,'')
  
  select @colsName
  
  set @query 
  = 'SELECT partnum, ' + @colsName + ' 
   from 
   (
    select 
      partnum, 
      period +''_''+col col, 
      value
    from
    (
      select partnum, 
        period,
        cast(TotalQty as numeric(10, 2)) TotalQty,
        cast(ToldSold as numeric(10, 2)) ToldSold
      from #orderhistory
    ) src
    unpivot
    (
      value
      for col in (TotalQty, ToldSold)
    ) unpiv
   ) s
   pivot 
   (
     sum(value)
     for col in (' + @cols + ')
   ) p 
   order by partnum'
  
  execute(@query)
  </code></pre>
  
  <p>It creates data like the following</p>
  
  <pre><code>partnum 2016-02 QtySold 2015-08Total $ Sold 2015-11Total $ Sold 2015-12 QtySold
  
  005483-10   NULL    NULL    NULL    100.00
  
  005483-12   NULL    NULL    1249.68 450.00
  
  005483-14   NULL    NULL    NULL    70.00
  
  005483-2    NULL    NULL    1234.19 350.00
  
  005483-3    10.00   NULL    NULL    NULL
  </code></pre>
  
  <p>What I would like to see is the headers go across in date order</p>
  
  <pre><code>partnum | 2015-08 QtySold | 2015-08 Total $ Sold | 2015-09 QtySold | 2015-09 Total $ Sold........
  </code></pre>
  
  <p>All the way across until I get to my current month.</p>
  
  <p>I think I need to add an order by somewhere, I just don't know where.  This is my first multiple column dynamic pivot table so I'm a little lost.  Everywhere i have tried has given me some sort of error.</p>
  
  <p>Any help is greatly appreciated!!</p>