添加Total列到动态SQL数据透视

Question

有很多这类问题，但是找不到任何符合我所寻找的。

我使用的代码来自另一个工作很好的帖子，但我想修改它，在表的底部添加一个总计行。

前一篇文章，所以创造者得到的荣誉是:SQL Server动态枢轴与多列

我当前的表是这样的:

<表类> Work_Group Avg 01/01/2021 01/01/2021 01/01/2021 等… tbody><<tr>蓝色5258绿色5258

您可以将GROUPING SETS ((mcp.Work_Group),())添加到当前查询中以获得小计。为此，需要创建另一个参数@colsEAST2，以便将生成的列标题日期列表保存为

SUM([2001-01-01]) AS [2001-01-01],SUM([2001-01-02]) AS [2001-01-02],SUM([2001-01-03]) AS [2001-01-03]

和容纳

的@colsEAST

[2001-01-01],[2001-01-02],[2001-01-03]

DECLARE @colsEAST       AS NVARCHAR(MAX);
DECLARE @colsEAST2      AS NVARCHAR(MAX);
DECLARE @pivotCountEAST AS NVARCHAR(MAX);
DECLARE @sqlEAST        AS NVARCHAR(MAX);
SELECT @colsEAST = ISNULL(@colsEAST + ', ', '') + QUOTENAME(Load_Date)
FROM (SELECT DISTINCT Load_Date 
FROM ##CE_tmpEast_ALL) AS Load_Date;    
SELECT @colsEAST2 = ISNULL(@colsEAST2 + ', ', '') 
+ ' SUM('+QUOTENAME(Load_Date)+') AS '+QUOTENAME(Load_Date)
FROM (SELECT DISTINCT Load_Date 
FROM ##CE_tmpEast_ALL) AS Load_Date;    
SET @pivotCountEAST = N'SELECT Work_Group, ' + @colsEAST2 +'
FROM (SELECT Work_Group, Load_Date, Count 
FROM ##CE_tmpEast_ALL) AS Count
PIVOT(SUM(Count) FOR Load_Date IN (' + @colsEAST + ')) AS pvt
GROUP BY Work_Group';                             
SET @sqlEAST = '; WITH MTD_CountPivot AS (
'+@pivotCountEAST+'
), MTD_Pivot_Tables AS (
SELECT Work_Group, AVG(Count) AS [AVG Count]
FROM ##CE_tmpEast_ALL
GROUP BY Work_Group
)
SELECT CASE WHEN GROUPING(mcp.Work_Group) = 0 
THEN mcp.Work_Group 
ELSE ''TOTAL'' 
END AS Work_Group,
SUM(mpt.[AVG Count]) AS Avg, 
' + @colsEAST2+ '
FROM MTD_CountPivot mcp
JOIN MTD_Pivot_Tables mpt 
ON mcp.Work_Group = mpt.Work_Group
GROUP BY GROUPING SETS ((mcp.Work_Group),())';    
EXEC sp_executesql @sqlEAST;