SQL Dynamic Pivot-如何对列进行排序 [英] SQL Dynamic Pivot - how to order columns

问题描述

我正在对包含以下内容的表进行动态数据透视查询:

I'm working on a dynamic pivot query on a table that contains:

• OID-订单ID
• 尺寸-产品尺寸
• BucketNum-大小的顺序 应该去
• 数量-订购的数量

• OID - OrderID
• Size - size of the product
• BucketNum - the order that the sizes should go
• quantity - how many ordered

size列包含不同的大小，具体取决于OID.

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 )

这很好用，只是列标题(大小标签)的顺序不符合bucketnum列的顺序.的顺序取决于尺寸.

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?

谢谢

推荐答案

您需要解决此问题:

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

以正确的顺序返回列.您可能必须执行以下操作，而不是使用DISTINCT:

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)

这篇关于SQL Dynamic Pivot-如何对列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！