SQL Server 中的 Cube 和事件处理器的动态透视

Herman<T>.Instance

4.91/5 (18投票s)

2011年5月25日

CPOL

6分钟阅读

56762

850

本文介绍了动态透视表的创建。这适用于 SQL Server 2005 及更高版本！

下载源代码 - 3.48 KB

DynamicPivoting/ProjectImage.png

引言

本文介绍了动态透视表的创建。它首先从一个简单的透视开始。第二步是添加用于计算总计的行。在这种情况下，您可以看到 Transact SQL 具有事件处理程序，并且与 WITH CUBE 命令结合使用时，它们非常有用。不幸的是，SQL Server (2005 及更高版本) 中的 PIVOT 命令使用命名列名。要使其动态化，需要做一些编程。

背景

我的一位客户一直在我们的系统中加载增量数据，直到他发现可以将所有数据每月添加到我们的系统中。我们想看看系统的增长速度有多大。他开始加载完整文件而不是增量文件的那个月在结果中显著显示出来。当我们创建了数据的 Excel 图表时，所有人都惊呆了。通过添加总计，创建了一个简单的管理报告，它非常直观地告诉我们的客户我们的系统有多强大。

Using the Code

本文使用的数据库是 AdventureWorks ，您可以在此处找到它。随附于此项目的查询可以在 SQL Server Management Studio (SSMS) 中针对 SqlServer 2008 R2 运行。Adventureworks 数据库名称是 AdventureWorksDW2008R2。

开始 - 通常的终点

大多数开发人员都了解一些 SQL，当他们需要创建显示两件事之间相关性的查询时，会出现如下查询

 SELECT var1, var2, count(var2) from table1 group by var1, var2

如果我们查看 AdventureWorksDW2008R2 数据库，FactSalesQuota 表可能会引出下一个问题。基于 CalendarYear、CalendarQuarter 和 SalesAmountQuota 字段，可以调查每个季度每年售出了多少 Amount 。开始时的查询可以是

SELECT [CalendarYear],[CalendarQuarter],[SalesAmountQuota] _
	FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]

结果是一长串数字，通过 SUM() 函数和 GROUP BY 进行“改进”。但这 3 列什么也说明不了。无法看出相关性。

如何开始动态化

这个“改进”后的查询是动态透视的起点。如果我们想在 CalendarYear 和 CalendarQuarter 之间看到有效的结果，我们需要采取四个步骤：

将查询结果存储在临时表中。
查找 CalendarQuarter 列中的唯一值，并将它们设置为 varchar 格式的列名。
创建透视命令（基于使用定义的列名的透视命令），存储在 varchar 中。
执行创建的透视命令。
我说的是四个步骤，但第五步经常被遗忘：删除临时表。

现在开始编码

开始将查询结果存储在临时表中

SELECT [CalendarYear],[CalendarQuarter],SUM([SalesAmountQuota]) as SalesAmount
INTO	#tempPivotTable
FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
GROUP BY [CalendarYear],[CalendarQuarter]

然后，基于 CalendarQuarter 列中的唯一值创建 columnnames 。每个值都应强制转换为 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 开头，然后是值。它代表了多年来每个季度的总和。但第一个问题并未得到解答：我们每年的销量是否在增长？此时，我们需要 Transact SQL 中的事件处理程序。别担心！我会帮助您。需要的事件处理程序是“分组”。当 group by 在 SQL Server 中执行时，您希望在第一列显示 'Total'，否则显示 calenderyear。因此，我们还需要 '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 是 integer 类型。这就是为什么添加了转换为 varchar （包括在 group by 中）。所以我们有点幸运！如果 calendaryear 有其他值，例如以 'name_an_animal' 的年份为起点，则 Total 行将是结果的开头。文章末尾提供了一个解决方案。如果需要添加 Total 列，它应该包含在 CalendarQuarter 的集合中。因此，我们还需要 GROUPING 来处理 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'，您总可以将总计行和列放在最后。

最终想法

如果使用 year 而不是 quarter 作为 columnnames ，您将真正看到此查询的强大功能。如果将 2009 年的数据添加到表中，该年份将立即作为新列出现在结果集中。这使得这类查询非常方便用于：

管理报告
出现问题时快速扫描值
使您数据的真正价值可视化

历史

2011-5-24: 1.0 初始版本
2011-5-24: 1.1 修正了拼写错误，因为我开始时对另一个表进行了透视