文档介绍:SQLServer:SQL实现动态交叉表
GO
SET ANSI_NULLS ON
GO
ALTER procedure CrossTable
***@strTableName as varchar(50)=’’, --
SQLServer:SQL实现动态交叉表
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 ’