如何在使用动态列标题集的透视查询的末尾添加Grand total列?

Question

我正在为客户端创建一个报告，该报告要求我生成一个使用一组动态列标题的Pivot查询。我已经做到了这一点，然而我正在努力在最后介绍一个工作总。我知道如何做到这一点，如果值是固定的，但不使用一组动态列标题。任何帮助都将是非常感激的:)

-- Construct the dynamic PIVOT query
DECLARE @cols AS NVARCHAR(MAX)
-- Get the distinct rates
SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(rate)
FROM (SELECT DISTINCT rate FROM #PayDetailNAme pay JOIN #DutyInfo2 duty ON pay.DUTY_ID = duty.Dutyid ) AS rate;
DECLARE @query AS NVARCHAR(MAX)
-- Construct the dynamic PIVOT query
SET @query = '
SELECT

[start],
[end],
EMPLOYEE,
pin,
' + @cols + '
FROM 
(
SELECT 
''' + @start + ''' as [Start],
''' + @end + ''' as [End],
EMPLOYEE,
pin, 
Rate,
isnull(hours,0) as hours
from #PayDetailNAme pay 
JOIN #DutyInfo2 duty 
ON pay.DUTY_ID = duty.Dutyid
) 
AS SourceTable
PIVOT
(
SUM(hours)
FOR rate IN (' + @cols + ')
) 
AS PivotTable
WHERE COALESCE(' + @cols + ') IS NOT NULL
;';

我已经看了一些在线示例，但无法使此行为如预期的

Answer 1

正如我在评论中提到的那样，使用条件聚合比使用限制性PIVOT操作符要容易得多。对于条件聚合，当您需要汇总列时，只需使用不带条件的SUM:

SUM(CASE SomeColumn WHEN 'SomeValue' THEN SomeNumeric END) AS SomeValue,
SUM(SomeNumeric) AS SomeTotal

对于动态语句，这没有任何不同，您只需将SUM放在动态条件聚合之后。假设SQL Server 2017+(并且您有STRING_AGG作为结果)，并使用组成的样本数据(因为问题中没有)，这会导致如下查询:

CREATE TABLE dbo.YourTable (GroupingID int,
SomeValue varchar(10),
SomeInt int);
GO
INSERT INTO dbo.YourTable (GroupingID,
SomeValue,
SomeInt)
VALUES(1,'abc',10),
(1,'def',12),
(1,'abc',15),
(2,'abc',9),
(3,'xyz',12),
(3,'def',7),
(4,'xyz',12);
GO
DECLARE @ID int = 3;
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nchar(3) = N',' + @CRLF;

SELECT @SQL = N'SELECT GroupingID,' + @CRLF +
STRING_AGG(N'       SUM(CASE SomeValue WHEN ' + QUOTENAME(SomeValue,'''') + N' THEN SomeInt END) AS ' + QUOTENAME(SomeValue),@Delimiter) WITHIN GROUP (ORDER BY SomeValue) + N',' + @CRLF +
N'       SUM(SomeInt) AS GrandTotal' + @CRLF +
N'FROM dbo.YourTable' + @CRLF +
N'WHERE GroupingID <= @ID' + @CRLF + --Demonstrate parametrisation
N'GROUP BY GroupingID;'
FROM (SELECT DISTINCT SomeValue
FROM dbo.YourTable) YT;
--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
GO
DROP TABLE dbo.YourTable;