利用SQL存储过程创建交叉表

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go





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

根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计

注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分

*/

/**//*--调用示例

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

ALTERproc[dbo].[proc_ViewDriverDicpatch]
@TableNamesysname,--表名
@纵轴sysname,--交叉表最左面的列
@横轴sysname,--交叉表最上面的列
@表体内容sysname,--交叉表的数数据字段
@条件varchar(1000),--查询的处理条件
@是否加横向合计bit,--为1时在交叉表横向最右边加横向合计
@是否加纵向合计bit--为1时在交叉表纵向最下边加纵向合计
as
declare@snvarchar(4000),@sqlvarchar(8000)

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

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
/**//*
set@s='declare@asysname
if(selectcasewhencount(distinct[@纵轴])from'+@TableName+')=1
select@a=@纵轴,@纵轴=@横轴,@横轴=@a'
execsp_executesql@s
,N'@纵轴sysnameout,@横轴sysnameout'
,@纵轴out,@横轴out
*/
--生成交叉表处理语句
set@s='
set@s=''''
select@s=@s+'',[''+cast(['+@横轴+']asvarchar)+'']=sum(case['+@横轴
+']when''''''+cast(['+@横轴+']asvarchar)+''''''then['+@表体内容+']else0end)''
from['+@TableName+']
'+@条件+'
groupby['+@横轴+']'
execsp_executesql@s
,N'@svarchar(8000)out'
,@sqlout

--是否生成合计字段的处理
declare@sum1varchar(200),@sum2varchar(200),@sum3varchar(200)
select@sum1=case@是否加横向合计
when1then',[合计]=sum(['+@表体内容+'])'
else''end
,@sum2=case@是否加纵向合计
when1then'['+@纵轴+']=casegrouping(['
+@纵轴+'])when1then''合计''elsecast(['
+@纵轴+']asvarchar)end'
else'['+@纵轴+']'end
,@sum3=case@是否加纵向合计
when1then'withrollup'
else''end

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