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 包含不同的大小.

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.

我在数据透视后尝试了可选的 Order By，但这不起作用.

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屋！