在数据库分析中，数据透视表是一种强大的工具，它能够将数据从行格式转换为列格式，从而提供更直观的数据视图。本文将介绍如何使用SQL Server的动态数据透视功能来分析销售数据。通过一个实际案例，将展示如何构建动态透视表，以及如何使用WITH CUBE命令和事件处理器来增强查询结果。
在一次项目中，客户希望了解其系统数据的增长情况。以往，他们只加载数据的增量部分，直到发现可以每月将全部数据加载到系统中。想要观察系统增长的规模。当客户开始加载完整文件而非增量文件时，结果显著地显示出来。通过Excel图表展示数据后，所有人都对结果感到惊讶。通过添加总计，创建了一个简单的管理报告，直观地展示了系统的能力。
本文使用的数据库是AdventureWorks，可以在找到。与本文相关的查询可以在SQLServer Management Studio (SSMS)中运行，适用于SqlServer 2008 R2。数据库名称为AdventureWorksDW2008R2。
大多数开发者都熟悉一些SQL，当他们需要创建一个显示两个事物之间相关性的查询时，通常会写出如下的查询：
SELECT var1, var2, COUNT(var2) FROM table1 GROUP BY var1, var2
如果查看AdventureWorksDW2008R2数据库中的FactSalesQuota表，基于字段CalendarYear、CalendarQuarter和SalesAmountQuota，可以探究每个季度每年的销售额。起始查询可能是：
SELECT [CalendarYear], [CalendarQuarter], [SalesAmountQuota] FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
结果是一个长列表，通过SUM()函数和GROUP BY进行了“改进”。但是，这三个列并没有表达出任何相关性。
“改进”的查询是动态透视的起点。如果想要看到CalendarYear和CalendarQuarter之间的有效结果，需要采取以下四个步骤：
说了四个步骤，但第五步经常被遗忘：删除临时表。
首先，将查询结果设置在临时表中：
SELECT [CalendarYear], [CalendarQuarter], SUM([SalesAmountQuota]) as SalesAmount INTO #tempPivotTable FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota] GROUP BY [CalendarYear], [CalendarQuarter]
然后，基于CalendarQuarter列中的唯一值创建列名。每个值应该被转换为varchar类型，并放在括号中，列就准备好了！为了创建@columns，通常的做法是：
@columns = @columns + '[' + '.....' + ']'
这段代码的危险在于，当'[' + '.....' + ']'失败时，不知道@columns会是什么。因此，更喜欢使用STUFF命令。它是一个函数，结果被放在变量中。
DECLARE @columns VARCHAR(8000) SELECT @columns = STUFF((SELECT DISTINCT TOP 100 PERCENT '], [' + CAST([CalendarQuarter] as varchar) FROM #tempPivotTable AS t2 ORDER BY '], [' + CAST([CalendarQuarter] as varchar) FOR XML PATH('')), 1, 2, ']')
然后创建收集数据的透视查询。记住，需要一个聚合函数来使透视工作。在这种情况下，可以使用MIN()或MAX()，因为它是每个季度每年的一个金额。
DECLARE @query VARCHAR(8000) SET @query = 'SELECT * FROM #tempPivotTable PIVOT (MAX(SalesAmount) FOR [CalendarQuarter] IN (' + @columns + ')) AS p'
执行创建的查询：
EXECUTE(@query)
删除临时表。否则，不能连续运行这个查询两次。
DROP TABLE #tempPivotTable
在项目图像中，可以看到结果。如果看到一个字段中的值为NULL，知道该组合是不可用的。
拥有透视表很好，但下一个问题是来自经理。每年销售更多吗？每个季度的销售是基于多年的吗？是时候在查询运行时对行进行一些计数了。首先需要在基本查询中使用WITH CUBE命令。
SELECT [CalendarYear], [CalendarQuarter], SUM([SalesAmountQuota]) as SalesAmount INTO #tempPivotTable FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota] GROUP BY [CalendarYear], [CalendarQuarter] WITH CUBE
如果运行这个查询，顶部会添加一行，以NULL开头，然后是值。它代表了每个季度多年来的总和。但第一个问题还没有回答：每年销售更多吗？在这一点上，需要在transactSQL中使用事件处理器。放松！会帮。需要的事件处理器是'grouping'。当SQL服务器执行group by时，希望在第一列显示'Total'，否则是calendaryear。所以也需要'case when'函数来执行这个'if'。基础查询会改变：
SELECT CASE WHEN GROUPING(CAST([CalendarYear] as varchar)) = 1 THEN 'Total' ELSE CAST([CalendarYear] as varchar) END as [CalendarYear], [CalendarQuarter], SUM([SalesAmountQuota]) as SalesAmount INTO #tempPivotTable FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota] GROUP BY CAST([CalendarYear] as varchar), [CalendarQuarter] WITH CUBE
在这种情况下，'Total'是一个varchar，而CalendarYear是一个整数。这就是为什么在group by中添加了转换为varchar的原因。这就是为什么有点幸运！如果calendaryear有另一个值，比如'year of the name_an_animal'作为起点，那么Total行将开始结果。文章最后给出了解决方案。如果需要添加Total列，它应该在CalendarQuarter的集合中。所以也需要GROUPING for CalendarQuarter。基础查询再次扩展：
SELECT CASE WHEN GROUPING(CAST([CalendarYear] as varchar)) = 1 THEN 'Total' ELSE CAST([CalendarYear] as varchar) END as [CalendarYear], CASE WHEN GROUPING(CAST([CalendarQuarter] as varchar)) = 1 THEN 'Total' ELSE CAST([CalendarQuarter] as varchar) END as [CalendarQuarter], SUM([SalesAmountQuota]) as SalesAmount INTO #tempPivotTable FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota] GROUP BY CAST([CalendarYear] as varchar), CAST([CalendarQuarter] as varchar) WITH CUBE
如果运行总计查询，会看到：
总计查询是：
SELECT CASE WHEN GROUPING(CAST([CalendarYear] as varchar)) = 1 THEN 'Total' ELSE CAST([CalendarYear] as varchar) END as [CalendarYear], CASE WHEN GROUPING(CAST([CalendarQuarter] as varchar)) = 1 THEN 'Total' ELSE CAST([CalendarQuarter] as varchar) END as [CalendarQuarter], SUM([SalesAmountQuota]) as SalesAmount INTO #tempPivotTable FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota] GROUP BY CAST([CalendarYear] as varchar), CAST([CalendarQuarter] as varchar) WITH CUBE DECLARE @columns VARCHAR(8000) SELECT @columns = STUFF((SELECT DISTINCT TOP 100 PERCENT '], [' + CAST([CalendarQuarter] as varchar) FROM #tempPivotTable AS t2 ORDER BY '], [' + CAST([CalendarQuarter] as varchar) FOR XML PATH('')), 1, 2, '') + ']' DECLARE @query VARCHAR(8000) SET @query = 'SELECT * FROM #tempPivotTable PIVOT (MAX(SalesAmount) FOR [CalendarQuarter] IN (' + @columns + ')) AS p' EXECUTE(@query) DROP TABLE #tempPivotTable
在底部的总行解决方案中，关键是'Total'以't'开头，而'u,v,w,x,y,z'可能导致总行出现在结果集的中间。在这种情况下，应该查看数据库中使用的排序规则。在情况下，它是：
Latin1_General_CI_AS
如果查看，可以看到字符161在'z'之后。知道'¡Total'现在看起来不好看，但行和/或列是清晰可识别的。然而，效果是第二列和第一行代表总值。看起来更难看，但如果从'ZZ'开始'Total'，总是可以结束总行和列。