Pivot table with dynamic columns in date order

Question

I have a pivot table creating 2 columns for each line that I would like to go in date order.

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

I have the following code to generate the dynamic pivot table

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)

It creates data like the following

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

What I would like to see is the headers go across in date order

partnum | 2015-08 QtySold | 2015-08 Total $ Sold | 2015-09 QtySold | 2015-09 Total $ Sold........

All the way across until I get to my current month.

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.

Any help is greatly appreciated!!

Answer 1

you would order your @colNames data..

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
--ORDER BY HERE
    ORDER BY period, c.col
    FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'')