Sort Columns For Dynamic Pivot

Question

I have the following SQL query where the columns created are out of order and I am not quite sure how to fix it.

SELECT rhead.rhcust AS [Cust ID], rdetl.rdextp AS [Inv Amt], rhead.rhivdt AS [Inv Date]
INTO #TempTable
FROM rhead
LEFT OUTER JOIN rdetl
    ON rhead.rhinvc = rdetl.rdinvc
WHERE rhead.rhivdt >= '01-01-2012' AND rhead.rhivdt <= '12-25-12'

ALTER DATABASE Vista_TM SET COMPATIBILITY_LEVEL = 100

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT [Cust ID],' + @cols + ' 
            from 
            (
                SELECT [Cust ID], [Inv Amt],
                  ''Month''+cast(DATEPART(m, [Inv Date]) as varchar(2)) MonthNo
                FROM #TempTable
            ) x
            pivot 
            (
                sum([Inv Amt])
                for MonthNo in (' + @cols + ')
            ) p '

execute(@query)

DROP TABLE #TempTable

I believe it has to do something with this section of the query:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

I did try messing around with the answer posted in this topic but I could not get the query to run. I am hoping someone can help.

Edit I just noticed my rows are out of order and would like to sort by [Cust ID] as well.

Answer 1

You can adjust the order of the fields in a dynamic pivot query by adding an ORDER BY when you set your @cols string:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) 
                    from #TempTable
                    ORDER BY ....
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

Update: Missed the DISTINCT at first, when using DISTINCT you'll have to use a subquery and then ORDER BY:

SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(ColName) 
                    FROM (SELECT DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName
                          FROM #TempTable
                          )sub
                    ORDER BY ColName
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

You may need to add a 'sort' field into your subquery if you can't simply use the column name, and you can add any fields to the subquery so long as they don't disrupt the DISTINCT list. For example:

SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(ColName) 
                    FROM (SELECT DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName
                                          ,CASE WHEN field = 'something' THEN 1
                                                WHEN field = 'something else' THEN 2
                                                ELSE 3
                                           END as Sort
                                          ,Cust_ID
                          FROM #TempTable
                          )sub
                    ORDER BY Sort,Cust_ID
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')