SQL Dynamic Pivot - how to order columns

dynamic-pivot, pivot, sql-server-2005

Solution

You need to fix this:

SELECT  @listCol = STUFF(( SELECT distinct  '], [' + [size]
                           FROM     #t
                         FOR
                           XML PATH('')
                         ), 1, 2, '') + ']'

To return the columns in the right order. You might have to do something like this instead of using `DISTINCT`:

SELECT [size]
FROM     #t
GROUP BY [size]
ORDER BY MIN(BucketNum)

Problem

I'm working on a dynamic pivot query on a table that contains: - OID - OrderID - Size - size of the product - BucketNum - the order that the sizes should go - quantity - how many ordered The size column contains different sizes depending upon the OID. So, using the code found here, I put this together: ``` DECLARE @listCol VARCHAR(2000) DECLARE @query VARCHAR(4000) SELECT @listCol = STUFF(( SELECT distinct '], [' + [size] FROM #t FOR XML PATH('') ), 1, 2, '') + ']' SET @query = 'SELECT * FROM (SELECT OID, [size], [quantity] FROM #t ) src PIVOT (SUM(quantity) FOR Size IN (' + @listCol + ')) AS pvt' EXECUTE ( @query ) ``` This works great except that the column headers (the sizes labels) are not in the order based upon the bucketnum column. The are in the order based upon the sizes. I've tried the optional Order By after the pivot, but that is not working. How do I control the order in which the columns appear? Thank you

Original source