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