在数据库管理与分析中，数据透视表是一种常用的数据汇总方法，它能够将行数据转换成列数据，从而实现数据的聚合展示。SQL Server 2005引入了PIVOT操作，使得在SQL中实现数据透视表变得简单。本文将探讨如何在SQL中使用PIVOT和UNPIVOT操作，并重点介绍如何添加总计行和列。
作为一名.NET开发者，通常会从存储过程获取数据透视表，并在C#/VB代码中使用Datatable来计算总计行和列。但本文将展示如何在存储过程中直接获取这些总计，以便在不进行任何C#/VB代码操作的情况下直接在网格中显示数据。
PIVOT操作可以将表值表达式中的一列的唯一值转换为输出中的多个列，并在需要的地方对任何剩余的列值进行聚合。相反，UNPIVOT操作将表值表达式的列转换为列值。
在实际应用中，经常需要动态地生成PIVOT操作中的列列表。以下是一个简单的SQL脚本，展示了如何动态生成PIVOT操作的列列表：
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ', [' + colName + ']', '[' + colName + ']')
FROM Table1
WHERE Conditions
ORDER BY colName
PRINT @cols
以下SQL脚本创建了一个存储过程，该过程返回数据透视表作为输出：
CREATE PROCEDURE pivot_TeamVsMatches AS
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 Query :' + @FinalQuery
EXECUTE (@FinalQuery)
GO
为了在数据透视表中添加总计行和列，需要构建COALESCE查询。以下是生成总计列的SQL脚本：
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)
以下是生成总计行的SQL脚本：
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
以下是完整的存储过程，它将生成包含总计行和列的数据透视表：
CREATE PROCEDURE pivot_TeamVsMatches AS
DECLARE @columnHeaders NVARCHAR(MAX)
SELECT @columnHeaders = COALESCE(@columnHeaders + ', [' + month + ']', '[' + month + ']')
FROM tbl_Matches
GROUP BY month
ORDER BY month
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)
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
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 (@FinalQuery)
GO