產生交叉表的簡單通用預存程序

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時在交叉表縱向最下邊加縱向合計
as
declare @s nvarchar(4000),@sql varchar(8000)

--判斷橫向欄位是否大於縱向欄位數目,如果是,則交換縱橫欄位
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)
go