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

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





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

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

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

*/

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

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

ALTER proc [ dbo ] . [ proc_ViewDriverDicpatch ]
@TableName sysname, -- 表名
@纵轴 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@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[ ' + @横轴 + ' ] '
exec sp_executesql @s
,N ' @svarchar(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 ' [ ' + @纵轴 + ' ]=casegrouping([ '
+ @纵轴 + ' ])when1then '' 合计 '' elsecast([ '
+ @纵轴 + ' ]asvarchar)end '
else ' [ ' + @纵轴 + ' ] ' end
, @sum3 = case @是否加纵向合计
when 1 then ' withrollup '
else '' end

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