SQLServer：SQL實(shí)現(xiàn)動(dòng)態(tài)交叉表

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)=’’,--被統(tǒng)計(jì)的字段
    @strCompute as varchar(50)=’Sum’--運(yùn)算方式
    as
    declare @strSql as varchar(1000),@strTempCol as varchar(100)
    execute (’DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ’+@strCol+’ from ’+@strTableName+’ for read only’) --生成游標(biāo)
    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