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

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@#,1,1--*/

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

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段set @s=@#declare @a sysnameif(select case when count(distinct [@#+@纵轴+@#])<count(distinct [@#+@横轴+@#]) then 1 else 0 endfrom [@#+@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

 

原文转自：http://www.ltesting.net