SQL Dynamic Pivot - how to order columns
32,799
Solution 1
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)
Solution 2
SELECT @listCol = STUFF(
(SELECT DISTINCT ',' + QUOTENAME(size) AS [size]
FROM #t
ORDER BY [size]
FOR XML PATH('')
Author by
Chris Burgess
Updated on June 12, 2020Comments
-
Chris Burgess almost 4 years
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
-
Chris Burgess almost 15 yearsAhhhhh! The 'MIN(BucketNum)' bit was what I needed!! Thank you, thank you!
-
Cade Roux almost 15 yearsThat solution is non-dynamic only in names of columns, not in number of columns, which still would need a dynamic technique at the time of the pivot operation. I have used that technique for pivoting variable date ranges however where it's always 12 months, but starts at different months - it's a basic sliding transform.
-
JAL almost 9 yearsAlthough this query may answer the question, can you provide a little more information about how this answer works?
-
frustratedInFresno almost 9 yearsIt works the same was as the code in the original post but it creates an alias for the column so that it may have an ORDER BY applied to it.