SQL交叉表实例

[2005-4-8 10:36:35 | By: 孤独的猎人]

SQL交叉表实例

建表：

在查询分析器里运行：
CREATETABLE [Test] (
[id][int] IDENTITY (1, 1) NOT NULL ,
[name][nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject][nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source][numeric](18, 0) NULL
)ON [PRIMARY]
GO

INSERTINTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)

INSERTINTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)

INSERTINTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)

INSERT INTO [test] ([name],[subject],[Source]) values(N'王五',N'数学',75)

INSERTINTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)

INSERT INTO [test] ([name],[subject],[Source]) values(N'张三',N'英语',100) INSERT INTO [test] ([name],[subject],[Source]) values(N'王五',N'语文',57)

Go

交叉表语句的实现：

--用于：交叉表的列数是确定的
selectname,sum(case subject when '数学'then source else 0 end) as '数学',sum(case subject when '英语'then source else 0 end) as '英语',
sum(casesubject when '语文'then source else 0 end) as '语文'
fromtest
groupby name

--用于：交叉表的列数是不确定的

thensource else 0 end) as '''+subject+''','

from(select distinct subject from test) as a

select@sql = left(@sql,len(@sql)-1) + ' from test group by name'

exec(@sql)

go

一个通用的针对单表用的交叉表存储过程

传入几个参数：

@TableNamevarchar(16) --表名
@纵轴varchar(20)--交叉表最左面的列
@横轴varchar(10)--交叉表最上面的列
@表体内容numeric(10,2)--交叉表的数字内容
@是否加横向合计bit--为1时在交叉表横向最右边加横向合计
@是否家纵向合计bit--为1时在交叉表纵向最下边加纵向合计

思路：
1、取得disinct横轴字段后，取得唯一的横轴字段表

像select科室,sum(case(横轴字段表.....).......)from @tablename group by 科室 2、根据横轴的唯一字段内容，循环整个表后动态生成一个Sql语句，

3、根据参数是否合计，分别加合计字段，求出横向合计和纵向合计

您看这个思路行吗？但有一个就是横轴不能太多，多了Sql可能会超过8000字符。一般不会这么多，如果太多就把横轴变为exec 生成的Sql

纵轴，总之取字段较少的做横轴，这个就是传参数时的问题了。

如果弄成了，这个在一定的范围内应该是比较通用的了。对不！

这是我的思路，具体写的时候，感觉到Sql的组合比较麻烦，能帮我写一下吗？

============================================================

ifexists (select * from dbo.sysobjects where id =object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') =1) drop procedure [dbo].[p_qry]
GO

/*--生成交叉表的简单通用存储过程

--邹建204.06--*/

/*--调用示例

execp_qry 'syscolumns','id','colid','colid','name like ''s%''',1,1
--*/

createproc p_qry
@TableNamesysname, --表名
@纵轴sysname,--交叉表最左面的列
@横轴sysname,--交叉表最上面的列
@表体内容sysname,--交叉表的数数据字段
@条件varchar(1000),--查询的处理条件
@是否加横向合计bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计bit--为1时在交叉表纵向最下边加纵向合计
as
declare@s nvarchar(4000),@sql varchar(8000)

--规范条件
set@条件=casewhen @条件<>''then ' where ('+@条件+')'else '' end

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set@s='declare @a sysname

select @a=@纵轴,@纵轴=@横轴,@横轴=@a'exec sp_executesql @s if(select case when count(distinct['+@纵轴+'])from ['+@TableName+']'+@条件+')=1

,@纵轴out,@横轴out

--生成交叉表处理语句 ,N'@纵轴sysname out,@横轴 sysnameout'

set@s='
set@s=''''
select@s=@s+'',[''+cast(['+@横轴+']as varchar)+'']=sum(case ['+@横轴
+']when ''''''+cast(['+@横轴+']as varchar)+'''''' then ['+@表体内容+']else 0 end)'' from ['+@TableName+']
'+@条件+'
groupby ['+@横轴+']'
execsp_executesql @s
,N'@svarchar(8000) out'
,@sqlout

--是否生成合计字段的处理
declare@sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200) select@sum1=case @是否加横向合计

+@纵轴+']as varchar) end'
else'['+@纵轴+']'end
,@sum3=case@是否家纵向合计
when1 then ' with rollup'
else'' end

--生成交叉表
exec('select'+@sum2+@sql+@sum1+'
from['+@TableName+']
'+@条件+'
groupby ['+@纵轴+']'+@sum3)
go