SQL Server中实现交叉表

转贴请注明出处: http://www.cnweblog.com/liufangzhu/archive/2008/09/07/291903.html 

我们总有类似这样的需求:

表结构和数据如下:

分店名

年份

季度

收入

分店1

2008

1

343

分店1

2008

2

23

分店1

2008

3

23

分店1

2008

4

44

。。。

。。。

。。。

。。。

分店N

2008

N

43

表格1

上表表示:各个分店各个季度的收入情况。

而客户需要的统计(报表)是这样的:各个分店各个季度的平均收入,结果应该如下表:

这里应该是斜线表头,不过弄不上来

季度1

季度2

季度3

季度4

分店1

分店1季度1平均值

。。。

。。。

。。。

。。。

。。。

分店N

表格2

这个问题其实就是把分店名distinct后做为行标识,我称之为行字段;然后把季度字段的值放到结果表中做列名,我称之为列字段;然后把收入字段按行字段分组进行avg操作,填入结果表中,行字段和列字段的交叉点处(这也许就是交叉表名称的由来吧),我称之为值字段。其中,行字段可以有多个,做法没有区别,不另做说明。

这个问题在ACCESS中就可以通过交叉表来解决,微软在access中有特殊的SQL语法可以直接写交叉表,或通过其向导直接生成,我家里的机器上没有装access,不给大家做演示。

那么在SQL中用SQL语句怎样实现呢?这得用到SQL中的case when语法,它的大致用法是这样的:

case表达式

when value1 then 返回定制值

when value2 then 返回定制值

when。。。

when valueN then 返回定制值N

else其它定制值

end

其中else为可选项,如果表达式的值不在任何一个分支中,则表达式返回null。具体应用可参见微软帮助文档。

实现语句如下:

select分店名,

avg(case季度 when 1 then 收入 end) as 季度1,

avg(case季度 when 2 then 收入 end) as 季度2,

avg(case季度 when 3 then 收入 end) as 季度3,

avg(case季度 when 4 then 收入 end) as 季度4

from table1 group by 分店名

下面我们来分析这个语句:

首先查询第一列“分店名”,并按其进行分组,这是统计结果的行字段,然后分了四列,分别是“季度1~“季度4”,它们的值是通过case when语法过滤后再avg来确定的,直接含义是“如果季度是*季度,我就avg它的值,否则按null进行avg操作”,这样,在“分店*”与“季度*”的交叉点中,只插入(其实是查询出)分店*和季度*的“收入”平均值。

当然这是最简单的情况,例如,本例中,列字段的值是固定的(只有四个),但大多数情况下是不固定的,假如本例中,表格中的数据年份有很多,不仅是2008年的数据,而且在明年、后年时,明年和后年的数据会依次录入到表中,客户如果要下面的报表,如何做呢?

这里应该是斜线表头,不过弄不上来

。。。。。。

1997

。。。。。。

2008

分店1

分店1

1997年平均收入值

。。。

。。。

。。。

。。。

。。。

分店N

这时候得拼出最终要执行的SQL语句再exec这个语句得到结果。拼的过程需借助游标。

语句如下:

declare @year int

declare @str varchar(8000)

declare @strYear varchar(4)

select @str = ''

declare cur cursor for select distinct 年份 from table1

open cur

while 1=1

begin 

    fetch next from cur into @year

    if @@fetch_status <> 0

    begin     

       break

    end

    select @strYear= convert(varchar(4),@year)

    select @str = @str+',avg(case 年份 when '+@strYear+' then 收入 end ) as ['+@strYear+']'

end

close cur

deallocate cur

select @str =' select 分店名'+ @str +' from table1 group by 分店名'

--select @str

exec (@str)


这样就实现了列字段值不固定的情况下的交叉表。

SQL SERVER 2005中直接提供了进行交叉表操作的语法,pivot,朋友们有兴趣研究一下,能节省一些代码。另外,在EXCEL中,可以通过插入透视表的方式实现交叉表操作,也是通过向导就可以,如果交叉表不做在项目中只为提个数的话,可以直接用EXCEL提取结果,很方便。――刘方柱2008-9-7

posted on 2008-09-07 10:31 汤汤汤 阅读(695) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。
该文被作者在 2015-01-03 23:03 编辑过
网站导航:

导航

常用链接

留言簿(3)

随笔档案(137)

收藏夹

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜