SQL实现动态交叉表【用游标实现】

 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  procedure CrossTable
@strTableName as varchar(50)='',  --查询表
@strCol as varchar(50)='',
@strGroup as varchar(50)='',--分组字段
@strNumber as varchar(50)='',--被统计的字段
@strCompute as varchar(50)='Sum'--运算方式
as
declare @strSql as varchar(1000),@strTempCol as varchar(100)
execute ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT '+@strCol+' from '+@strTableName+' for read only') --生成游标
begin
set nocount on
set @strSql='select '+@strGroup+','+@strCompute+'('+@strNumber+') as ['+@strNumber+']'
open corss_cursor
while(0=0)
begin
fetch next from corss_cursor
into @strTempCol
if(@@fetch_status <>0) break
set @strSql=@strSql+','+@strCompute+'( case '+@strCol+' when '''+@strTempCol+''' then '+@strNumber +' else 0 end ) as ['+@strTempCol+']'
end
set @strsql=@strSql+' from '+@strTableName+' group by '+@strGroup

print @strSql
execute(@strSql)
if @@error <>0 return @@error
print @@error
close corss_cursor
deallocate corss_cursor return 0
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO