包含两个结果集的SQL PIVOT

包含两个结果集的SQL PIVOT - 按日期重新排序结果列(SQL PIVOT with two result sets - reordering result columns by date)

我创建了一个存储过程来从表中提取数据，并根据一天中发生的次数显示汇总信息。列是动态的。

按日期降序排列列的最佳方法是什么？

查询：

--Declare Query DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) --Declare COUNT variables DECLARE @ColumnNameCOUNT AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateCOUNT AS NVARCHAR(MAX) --Declare SUM variables DECLARE @ColumnNameSUM AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateSUM AS NVARCHAR(MAX) --Get distinct COUNT values of the PIVOT Column SELECT @ColumnNameCOUNT= ISNULL(@ColumnNameCOUNT + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed COUNT values of the PIVOT Column SELECT @ColumnNameAgregateCOUNT = COALESCE(@ColumnNameAgregateCOUNT + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get distinct SUM values of the PIVOT Column SELECT @ColumnNameSUM= ISNULL(@ColumnNameSUM + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed SUM values of the PIVOT Column SELECT @ColumnNameAgregateSUM = COALESCE(@ColumnNameAgregateSUM + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT ' + @ColumnNameAgregateCOUNT + ' FROM [tbBackordersArchive] PIVOT(COUNT(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameCOUNT + ')) AS PVTTable WHERE [Warehouse] = ''01'' UNION ALL SELECT ' + @ColumnNameAgregateSUM + ' FROM [tbBackordersArchive] PIVOT(SUM(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameSUM + ')) AS PVTTable WHERE [Warehouse] = ''01''' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery

表结构：

CREATE TABLE [dbo].[tbBackordersArchive]( [ReportDate] [date] NOT NULL, [Warehouse] [nvarchar](4) NULL, [PartNumber] [nvarchar](26) NULL, [Description] [nvarchar](37) NULL, [PDCStock] [float] NULL, [Account] [nvarchar](10) NULL, [AccountName] [nvarchar](80) NULL, [CreditHold] [nvarchar](3) NULL, [CustomerRef] [nvarchar](22) NULL, [WIP] [int] NULL, [DateEdit] [nvarchar](12) NULL, [OrderedQuantity] [float] NULL, [CumulativeQuantity] [float] NULL, [OrderType] [nvarchar](3) NULL ) ON [PRIMARY]

I've created a stored procedure to pull data from a table and display summarised information based on the number of occurrences in a day. The columns are dynamic.

What's the best way to order the columns by date descending?

The query:

Table structure:

最满意答案

由于Rabbit在这里提出的是解决的代码（请参阅@ColumnNameAgregateSUM和@ColumnNameAgregateCOUNT中的ORDER BY：

--Declare Query DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) --Declare COUNT variables DECLARE @ColumnNameCOUNT AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateCOUNT AS NVARCHAR(MAX) --Declare SUM variables DECLARE @ColumnNameSUM AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateSUM AS NVARCHAR(MAX) --Get distinct COUNT values of the PIVOT Column SELECT @ColumnNameCOUNT= ISNULL(@ColumnNameCOUNT + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed COUNT values of the PIVOT Column SELECT @ColumnNameAgregateCOUNT = COALESCE(@ColumnNameAgregateCOUNT + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates ORDER BY [ReportDate] DESC --Get distinct SUM values of the PIVOT Column SELECT @ColumnNameSUM= ISNULL(@ColumnNameSUM + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed SUM values of the PIVOT Column SELECT @ColumnNameAgregateSUM = COALESCE(@ColumnNameAgregateSUM + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates ORDER BY [ReportDate] DESC --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT ' + @ColumnNameAgregateCOUNT + ' FROM [tbBackordersArchive] PIVOT(COUNT(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameCOUNT + ')) AS PVTTable WHERE [Warehouse] = ''01'' UNION ALL SELECT ' + @ColumnNameAgregateSUM + ' FROM [tbBackordersArchive] PIVOT(SUM(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameSUM + ')) AS PVTTable WHERE [Warehouse] = ''01''' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery

As Rabbit suggested here is the resolved code (please see the ORDER BY in @ColumnNameAgregateSUM and @ColumnNameAgregateCOUNT:

--Declare Query DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) --Declare COUNT variables DECLARE @ColumnNameCOUNT AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateCOUNT AS NVARCHAR(MAX) --Declare SUM variables DECLARE @ColumnNameSUM AS NVARCHAR(MAX) DECLARE @ColumnNameAgregateSUM AS NVARCHAR(MAX) --Get distinct COUNT values of the PIVOT Column SELECT @ColumnNameCOUNT= ISNULL(@ColumnNameCOUNT + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed COUNT values of the PIVOT Column SELECT @ColumnNameAgregateCOUNT = COALESCE(@ColumnNameAgregateCOUNT + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates ORDER BY [ReportDate] DESC --Get distinct SUM values of the PIVOT Column SELECT @ColumnNameSUM= ISNULL(@ColumnNameSUM + ',','') + QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates --Get summed SUM values of the PIVOT Column SELECT @ColumnNameAgregateSUM = COALESCE(@ColumnNameAgregateSUM + ', SUM(','Sum(')+ QUOTENAME([ReportDate]) + ') as '+ QUOTENAME([ReportDate]) FROM (SELECT DISTINCT [ReportDate] FROM [tbBackordersArchive]) AS BackorderDates ORDER BY [ReportDate] DESC --Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery = N'SELECT ' + @ColumnNameAgregateCOUNT + ' FROM [tbBackordersArchive] PIVOT(COUNT(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameCOUNT + ')) AS PVTTable WHERE [Warehouse] = ''01'' UNION ALL SELECT ' + @ColumnNameAgregateSUM + ' FROM [tbBackordersArchive] PIVOT(SUM(OrderedQuantity) FOR [ReportDate] IN (' + @ColumnNameSUM + ')) AS PVTTable WHERE [Warehouse] = ''01''' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery

更多推荐