php动态交叉表,SQLServer：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

SQLServer：SQL实现动态交叉表.doc

下载Word文档到电脑，方便收藏和打印[全文共700字]

编辑推荐：

下载Word文档