SQL 动态透视 - 如何对列进行排序

发布于 2024-07-26 17:08:01 字数 978 浏览 4 评论 0原文

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

OID - OrderID
Size - 产品的大小
BucketNum - 尺寸的顺序应该
数量 - 订购的数量

尺寸列包含不同的尺寸，具体取决于 OID。

因此，使用代码发现这里，我将其放在一起：

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 列的顺序排列的。它们是根据尺寸排列的。

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

如何控制列的显示顺序？

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

止于盛夏 2024-08-02 17:08:01

您需要解决此问题：

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

以正确的顺序返回列。您可能必须执行类似的操作，而不是使用 DISTINCT：

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

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)

回复收藏 0 原文

余罪 2024-08-02 17:08:01

SELECT @listCol = STUFF(
        (SELECT DISTINCT ',' + QUOTENAME(size) AS [size]
        FROM #t
        ORDER BY [size]
        FOR XML PATH('')

SELECT @listCol = STUFF(
        (SELECT DISTINCT ',' + QUOTENAME(size) AS [size]
        FROM #t
        ORDER BY [size]
        FOR XML PATH('')

回复收藏 0 原文

ヅ她的身影、若隐若现 2024-08-02 17:08:01

我今天刚刚看到这个链接，它使用 CTE 动态构建列列表（大概您可以订购），而不需要动态 sql：

http://blog.stevienova.com/2009/07/13/使用 ctes-to-create-dynamic-pivot-tables-in-sql-20052008/

回复收藏 0 原文

青巷忧颜 2024-08-02 17:08:01

我遇到了同样的问题，并尝试了上面建议的解决方案，但可能由于我的理解水平，无法使其发挥作用。我发现一个简单的技巧是创建一个临时表，其中的列标题使用 Order by 语句正确排序，然后将该列表拉入设置动态数据透视查询列名称的变量。

例如，请客

SELECT WeekNum INTO #T3 
FROM #T2 
GROUP BY WeekNum 
ORDER BY MIN(WeekNum) 

SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','') + QuoteName(WeekNum) 
FROM (SELECT WeekNum From #T3) AS WeekNum

吃饭。

希望对某人有帮助。

I had the same problem and tried the solution suggested above but, probably due to my level of understanding, couldn't get it to work. I found a simple hack was to create a Temp table with the column headers ordered correctly using Order by statements and then pull in that list to the variable that sets the dynamic pivot query column names.

e.g.

SELECT WeekNum INTO #T3 
FROM #T2 
GROUP BY WeekNum 
ORDER BY MIN(WeekNum) 

SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','') + QuoteName(WeekNum) 
FROM (SELECT WeekNum From #T3) AS WeekNum

Worked a treat.

Hope that helps someone.

回复收藏 0 原文

~没有更多了~