熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

SQL實現動態交叉表【用游標實現】

2013-11-13 12:39:33  來源: SQL語言 

  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_NULLS ON
  GO

  ALTER  procedure CrossTable
  @strTableName as varchar()=  查詢表
  @strCol as varchar()=
  @strGroup as varchar()=分組字段
  @strNumber as varchar()=被統計的字段
  @strCompute as varchar()=Sum運算方式
  as
  declare @strSql as varchar()@strTempCol as varchar()
  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(=)
  begin
  fetch next from corss_cursor
  into @strTempCol
  if(@@fetch_status <>) break
  set @strSql=@strSql++@strCompute+( case +@strCol+ when +@strTempCol+ then +@strNumber + else end ) as        [+@strTempCol+]
  end
  set @strsql=@strSql+ from +@strTableName+ group by +@strGroup

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

  GO
  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO


From:http://tw.wingwit.com/Article/program/SQL/201311/16332.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.