教你快速掌握一个交叉表的通用存储过程

CREATE   proc   p_qry   
  @TableName   sysname,   --表名   
  @纵轴   sysname,     --交叉表最左面的列   
  @横轴   sysname,     --交叉表最上面的列   
  @表体内容   sysname,   --交叉表的数数据字段   
  @是否加横向合计   bit,--为1时在交叉表横向最右边加横向合计   
  @是否家纵向合计   bit,   --为1时在交叉表纵向最下边加纵向合计   
  @where    varchar(400) --查询where条件
  as   
  declare   @s   nvarchar(4000),@sql   varchar(8000)   
    
  --判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
   
  set   @s='declare   @a   sysname
   
  if(select   case   when   count(distinct
   
['+@纵轴+'])<count(distinct   ['+@横轴+'])   
then   1   else   0   end   
  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+']  where ' + @where  + '    
  group   by   ['+@纵轴+']'+@sum3)
GO