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

set  ANSI_NULLS  ON
set  QUOTED_IDENTIFIER  ON
go





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

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

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

*/

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

exec p_qry 'syscolumns','id','colid','colid','name like ''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 @条件=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 )