SqlServer如何生成动态交叉表查询

时　间:2008-04-10 16:19:00
作　者:UMVSOFT整理   ID：16  城市：江阴
摘　要:SqlServer如何生成动态交叉表查询
正　文:

        为了说明问题，我们用SqlServer自带的事例数据库(Northwind)来进行验证，所有的例子请放到Northwind中运行，我可能会省略Use语句，所引用的表，都是Northwind中的，下面我就不再说明了
        我这里指的交叉表，就是象Access的交叉表查询一样的效果，比如Employees表中City字段代表了城市的名称，TitleOfCourtesy代表称呼，我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数（本统计没有任何实际意义，只是挑选一些记录包含重复内容的字段来说明情况），并显示成以下格式：（TitleOfCourtesy作为行，City作为列）TitleOfCourtesyLondonCityRedmondCitySeattleCityDr. Mr.12 Mrs. 2 Ms.5 4探讨这个问题之前，我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的Select查询就可以搞定：

Select TitleOfCourtesy,
SUM(CASE City WHEN 'London' THEN ReportsTo ELSE NULL END) AS [London City],
SUM(CASE City WHEN 'Redmond' THEN ReportsTo ELSE NULL END) AS [Redmond City],
SUM(CASE City WHEN 'Seattle' THEN ReportsTo ELSE NULL END) AS [Seattle City]
FROM Employees GROUP BY TitleOfCourtesy
        其中利用了CASE语句判断，如果是相应的列，则取需要统计的ReportsTo数值，否则取NULL，然后再合计

其中有两个常见问题说明一下：
a、用NULL而不用0是有道理的，假如用0，虽然求和函数SUM可以取到正确的数，但类似COUNT函数（取记录个数），结果就不对了，因为Null不算一条记录，而0要算，同理空字串（""）也是这样，总之在这里应该用NULL,这样任何函数都没问题。
b、假如在视图的设计界面保存以上的查询，则会报错“没有输出列”，从而无法保存，其实只要在查询前面加上一段：Create View ViewName AS ...，ViewName是你准备给查询起的名称，...就是我们的查询，然后运行一下，就可以生成视图了，对于其他一些设计器不支持  的语法，也可以这样保存。
        以上查询作用也很大，对于很多情况，比如按照季度统计、按照月份统计等列头内容固定的情况，这样就行了，但往往大多数情况下列头内容是不固定的，象City，用户随时可能删除、添加一些城市，这种情况，我们就需要用存储过程来解决：
总体思路其实很简单，首先检索列头信息，形成一个游标，然后遍历游标，将上面查询语句里Case判断的内容用游标里的值替代，形成一条新的Sql查询，然后执行，返回结果，就可以了，以下是我写的一个存储过程，供大家参考：
Create procedure CorssTab
@strTabName as varchar(50) = 'Employees', --此处放表名
@strCol as varchar(50) = 'City', --表头分组依据字段
@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段
@strNumber as varchar(50) = 'ReportsTo', --被统计的字段
@strSum as varchar(10) = 'Sum' --运算方式
AS
DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR Select DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
SET nocount ON
SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段

OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT FROM corss_cursor --遍历游标，将列头信息放入变量@strTmpCol
INTO @strTmpCol
if (@@fetch_status<>0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN '' + @strTmpCol + '' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾
EXECUTE(@strsql) --执行


IF @@error <>0 RETURN @@error --如果出错，返回错误代码
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 --释放游标，返回0表示成功
end
GO
几点说明：
a、这是一个通用存储过程，使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上，其中结果集的第二列我加了个合计列
b、为了测试方便，我在存储过程中设置了默认值，就是前面提到的Employees表，这样直接运行时就可以出来我上面提到的结果。
c、使用时，可以把上面的代码复制到企业管理器的查询设计界面Sql窗格，或者查询分析器里运行一下（注意正确选择NorthWind数据库），就可以生成一个存储过程：CorssTab，然后直接运行CorssTab，如果出现本文前面类似的窗格，就表示运行成功了。
d、假如用于其它表，首先需要在你的用户数据库里生成此存储过程（当然也可以放到Master里，然后再加个变量：@DataBase，赋值为数据库名称，然后在上面代码打开指定数据库，这样所有的数据库都可以调用它），当你调用时，采取以下格式：
CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, orderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'
       上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量，大家可以运行试一下效果，虽然列头显示的名称不恰当，但基本效果出来了，相信大家通过对我的代码再作简单修改，可以达到满意的交叉表效果。