SQL - 带总计列和行的透视

引言

Microsoft SQL Server 在 Microsoft SQL Server 2005 版本中引入了 PIVOT 和 UNPIVOT 命令作为对 T-SQL 的增强。 SQL 中的透视表能够以自定义聚合方式显示数据。PIVOT 通过将表达式中一列的唯一值转换为输出中的多列来旋转表值表达式，并在最终输出中需要对任何剩余的列值执行聚合操作。UNPIVOT 通过将表值表达式的列旋转为列值来执行与 PIVOT 相反的操作。

更多关于透视表的信息.

在本文中，我将重点介绍如何为透视表显示总计行和列，如下面的网格视图所示

在网格中，我将显示一个团队在每个月进行的比赛场次。 最后，我需要显示每个团队的总计（最后一列）——这个总计给出了团队的年计数。 同样，我需要一个总计行作为网格中的最后一行，这将给出所有团队在该特定月份进行的比赛场次。

背景

通常作为 .NET 开发者，首先我会从存储过程获取透视表到数据集，以及总计行和列。 我将使用 Datatable（Datatable compute 方法 以获得更好的性能）在 C#/VB 代码中进行操作。 但在本文中，我想展示如何从存储过程中获取总计行和列，以便我们可以直接在网格中显示数据，而无需在 C#/VB 中进行任何操作。

工作原理

在这里，我使用带有动态列的透视表，大多数问题都与 PIVOT 语句中的列列表有关。 此列表是固定的，但很多时候新列是在稍后阶段由报告决定的。 当我们将透视表与动态 SQL 混合使用时，此问题很容易解决，因此这里有一个非常简单的脚本，介绍如何动态生成 pivot 语句

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + colName + ']', '[' + colName + ']')
FROM    Table1
WHERE	Conditions
ORDER BY colName

PRINT @cols

上面的脚本以带逗号的 string 格式为您提供列的列表。 例如：[Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sept], [Oct], [Nov], [Dec]。 如果您将此动态查询传递给您的透视表，那么您的透视表列将动态显示。
下面的 SQL 脚本创建了存储过程，该过程将透视表作为输出返回。

CREATE PROCEDURE pivot_TeamVsMatches
AS

/*First get the dynamic columns query*/

DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = _
	COALESCE (@columnHeaders  + ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
ORDER BY month

DECLARE @FinalQuery NVARCHAR(MAX)

SET @FinalQuery = 	‘SELECT *
			FROM
				(SELECT Team,
					   Month
				FROM tbl_Matches
				) A
			PIVOT
				(
				 COUNT(*)
				 FOR ColName
				 IN (‘+@columnHeaders +’)
				) B
ORDER BY Team’
PRINT ‘Pivot Queuery :’+ @FinalQuery
EXECUTE (@FinalQuery)
GO

此存储过程返回透视表，如下所示

现在要获得总计行和列，我们需要形成 COALESCE 查询。 如下所示…

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL _
([' + CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

上面的查询返回如下…

@GrandTotalCol = ISNULL ([' + CAST (Jan AS VARCHAR) +'],0) + ISNULL _
([' + CAST (Feb AS VARCHAR) +'],0) + ISNULL ([' + CAST (March AS VARCHAR) +'],0) + _
…………. + ISNULL ([' + CAST (Dec AS VARCHAR) +'],0).

/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow	NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month

上面的查询返回如下…

@GrandTotalRow = ISNULL(SUM([' + CAST(Jan AS VARCHAR)+']),0) + _
ISNULL(SUM([' + CAST(Feb AS VARCHAR)+']),0) + ……… + _
ISNULL(SUM([' + CAST(Dec AS VARCHAR)+']),0).

上面的 COALESCE 字符串 需要在动态透视查询中使用…

下面是存储过程，它将给出我们要求的总输出。

CREATE PROCEDURE pivot_TeamVsMatches
AS
/* COLUMNS HEADERS */
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = COALESCE (@columnHeaders  _
+ ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
GROUP BY month
ORDER BY month

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL ([' + _
CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM	tbl_Matches
GROUP BY Month
ORDER BY Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow	NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month

/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 	‘SELECT *, ('+ @GrandTotalCol + ') _
AS [Grand Total] INTO #temp_MatchesTotal
			FROM
				(SELECT Team,
					   Month
				FROM tbl_Matches
				) A
			PIVOT
				(
				 COUNT (*)
				 FOR ColName
				 IN (‘+@columnHeaders +’)
				) B
ORDER BY Team
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'','''','+@GrandTotalRow +', _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
-- PRINT 'Pivot Query '+@FinalQuery
EXECUTE(@PivotQuery)
GO

结果如下…

在这个存储过程中，我使用了一个临时表来获取总计行。 我使用临时表完成了 UNION ALL； 别忘了删除临时表。

如有任何疑问和建议，请发邮件给我 @ narapareddy.shyam@gmail.com。 通过使用一些第三方控件，我们也可以实现这一点，但在这里我只关注典型的/标准的 SQL 查询。 如果有人有任何高效且不同的方法，请与我分享。

再次感谢。 再见。