建表： 
在查询分析器里运行： 
CREATE TABLE [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 
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80) 
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100) 
Go 

交叉表语句的实现： 
--用于：交叉表的列数是确定的 
select name,sum(case subject when '数学' then source else 0 end) as '数学', 
sum(case subject when '英语' then source else 0 end) as '英语', 
sum(case subject when '语文' then source else 0 end) as '语文' 
from test 
group by name 

--用于：交叉表的列数是不确定的 
declare @sql varchar(8000) 
set @sql = 'select name,' 
select @sql = @sql + 'sum(case subject when '''+subject+''' 
then source 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 


一个通用的针对单表用的交叉表存储过程 
传入几个参数： 

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

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

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

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

您看这个思路行吗？但有一个限制就是横轴不能太多，多了Sql可能会超过8000字符。一般不会这么多，如果太多就把横轴变为纵轴，总之取字段较少的做横轴，这个就是传参数时的问题了。 

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

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

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

if exists (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--*/ 

/*--调用示例 

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

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

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

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段 
set @s='declare @a sysname 
if(select case when count(distinct ['+@纵轴+'])from ['+@TableName+'] '+@条件+')=1 
select @a=@纵轴,@纵轴=@横轴,@横轴=@a' 
exec sp_executesql @s 
,N'@纵轴 sysname out,@横轴 sysname out' 
,@纵轴 out,@横轴 out 

--生成交叉表处理语句 
set @s=' 
set @s='''' 
select @s=@s+'',[''+cast(['+@横轴+'] as varchar)+'']=sum(case ['+@横轴 
+'] when ''''''+cast(['+@横轴+'] as varchar)+'''''' then ['+@表体内容+'] else 0 end)'' 
from ['+@TableName+'] 
'+@条件+' 
group by ['+@横轴+']' 
exec sp_executesql @s 
,N'@s varchar(8000) out' 
,@sql out 

--是否生成合计字段的处理 
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200) 
select @sum1=case @是否加横向合计 
when 1 then ',[合计]=sum(['+@表体内容+'])' 
else '' end 
,@sum2=case @是否家纵向合计 
when 1 then '['+@纵轴+']=case grouping([' 
+@纵轴+']) when 1 then ''合计'' else cast([' 
+@纵轴+'] as varchar) end' 
else '['+@纵轴+']' end 
,@sum3=case @是否家纵向合计 
when 1 then ' with rollup' 
else '' end 

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

Web 本站

发表评论 - 不要忘了输入验证码哦！
作者：	用户：  密码：   注册？ 验证：
评论： 禁止表情 禁止UBB 禁止图片 识别链接 识别关键字	表  情