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

为了说明问题，我们用SqlServer自带的事例数据库(Northwind)来进行验证，所有的例子请放到Northwind中运行，我可能会省略Use语句，所引用的表，都是Northwind中的，下面我就不再说明了

这里指的交叉表，就是象Access的交叉表查询一样的效果，比如Employees表中City字段代表了城市的名称，TitleOfCourtesy代表称呼，我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数(本统计没有任何实际意义，只是挑选一些记录包含重复内容的字段来说明情况)，并显示成以下格式：(TitleOfCourtesy作为行，City作为列)

探讨这个问题之前，我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的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表里每个客户年度订单数量，大家可以运行试一下效果，虽然列头显示的名称不恰当，但基本效果出来了，相信大家通过对我的代码再作简单修改，可以达到满意的交叉表效果。