MSSQL2000 通用交叉表查询存储过程

--这个是最好的改进版本，已经在企业用了两年没有问题

CREATE PROCEDURE P_Global_TomCrossTable
@vSourceTAB As Varchar(200), --表名
@vGroupbyField As Varchar(1000),--groupby字段名 行
@vPivotCol As Varchar(500), --需要横转纵的字段名 列
@FunctionStr as varchar(500),--函數串Sum(field) as name,必須以' , ' 結尾
@vStrWhere As Varchar(1000)='',--查询条件
@AggreGate as Varchar(1000)='',--
@IntoTableName as varchar(30)=null --歸檔到新表
AS
if @vStrWhere is null set @vStrWhere=''
if not(@vStrwhere='') set @vStrWhere=' Where '+@vStrWhere+' '
if @vPivotCol is null set @vPivotCol=''--無行轉列，當作普通聚集
if Rtrim(Ltrim(@vPivotCol))=''
if (@FunctionStr is not null)
if not(@FunctionStr='')
begin
set @FunctionStr=Left(@FunctionStr,Len(@FunctionStr)-1) --去掉最後的逗號
if @IntoTableName is null
execute('select '+@vGroupbyField+','+@FunctionStr+' from '+@vSourceTAB+@vStrWhere+' Group by '+@vGroupbyField+' Order By '+@vGroupByField)
else
execute('select '+@vGroupbyField+','+@FunctionStr+' Into '+@IntoTableName+' from '+@vSourceTAB+@vStrWhere+' Group by '+@vGroupbyField+' Order By '+@vGroupByField)
return
end
declare @Start int,@End int
declare @Str varchar(100)
declare @FunctionGroup as Varchar(500)--函數+需要计算的字段(数字类型)
declare @vFunction As Varchar(50)--sum 等聚集函数
declare @vTransFormCol As Varchar(50) --需要计算的字段（数字类型）
declare @vAsName as varchar(300)
Declare @StrSql As Varchar(8000)
Declare @StrSql2 As Varchar(8000)
Declare @StrSql3 As Varchar(8000)
Declare @StrSql4 As Varchar(8000)
Declare @StrSql5 As Varchar(8000)
Declare @StrSum As Varchar(650)
Declare @pCols As Varchar(8000)
--Print('Declare CursorCross Cursor For Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB+' '+@vStrWhere+' Order By ' +@vPivotCol+' For Read only ')
Execute('Declare CursorCross Cursor For Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB+' '+@vStrWhere+' Order By ' +@vPivotCol+' For Read only ')
Begin
Set Nocount On
Set @StrSql =' '
Set @StrSql2=' '
Set @StrSql3=' '
Set @StrSql4=' '
Set @StrSql5=' '
Open CursorCross
While (0=0)
Begin
Fetch Next From CursorCross Into @pCols
IF (@@Fetch_Status<>0) Break
set @FunctionGroup=@FunctionStr
while Len(@FunctionGroup)>0
begin
set @Start=CHARINDEX('(' ,@FunctionGroup ,0)
if @Start>0 set @vFunction=LEFT(@FunctionGroup, @Start-1)
--Print @vFunction
set @End=CHARINDEX(')' ,@FunctionGroup ,@Start)
if @End>0 set @vTransFormCol=SUBSTRING(@FunctionGroup, @Start+1,@End-@Start-1)
--Print @vTransFormCol
set @Start=CharIndex('as',@FunctionGroup,0)
set @End=CharIndex(',',@FunctionGroup,@Start)
if @Start>0 and @End>0 set @vAsName=Ltrim(SubString(@FunctionGroup,@Start+2,@End-@Start-2))
--Print @vAsName
set @Start=CHARINDEX(',' ,@FunctionGroup ,0)
if @Start>0 set @FunctionGroup=Right(@FunctionGroup,Len(@FunctionGroup)-@Start) --else break
-- Print @Str
-- Print @FunctionGroup
if @vAsName is null set @vAsName=''
else if not(@vAsName='') set @vAsName='|'+@vAsName
if len(@StrSql)<6000
IF @pCols Is Null set @StrSql=@StrSql +',' + @vFunction +'(Case '+@vPivotCol+' When null Then '+@vTransFormCol +' End) As '+'['+'null'+@vAsName+']'
else Set @StrSql=@StrSql +',' + @vFunction +'(Case '+@vPivotCol+' When ' + ''''+@pCols+''''+ ' Then '+@vTransFormCol +' End) As '+'['+@pCols+@vAsName+']'
else if len(@StrSql)>6000 and len(@StrSql2)<6000
Set @StrSql2=@StrSql2+',' + @vFunction +'(Case '+@vPivotCol+' When ' + ''''+@pCols+''''+ ' Then '+@vTransFormCol +' End) As'+'['+@pCols+@vAsName+']'
else if len(@StrSql)>6000 and len(@StrSql2)>6000 and len(@StrSql3)<6000
Set @StrSql3=@StrSql3+',' + @vFunction +'(Case '+@vPivotCol+' When ' + ''''+@pCols+''''+ ' Then '+@vTransFormCol +' End) As'+'['+@pCols+@vAsName+']'
else if len(@StrSql)>6000 and len(@StrSql2)>6000 and len(@StrSql3)>6000 and len(@StrSql4)<6000
Set @StrSql4=@StrSql4+',' + @vFunction +'(Case '+@vPivotCol+' When ' + ''''+@pCols+''''+ ' Then '+@vTransFormCol +' End) As'+'['+@pCols+@vAsName+']'
else if len(@StrSql)>6000 and len(@StrSql2)>6000 and len(@StrSql3)>6000 and len(@StrSql4)>6000 and len(@StrSql5)<6000
Set @StrSql5=@StrSql5+',' + @vFunction +'(Case '+@vPivotCol+' When ' + ''''+@pCols+''''+ ' Then '+@vTransFormCol +' End) As'+'['+@pCols+@vAsName+']'
else
begin
Close CursorCross
Deallocate CursorCross
Raiserror 51000 'Column too much too long'
return
end
End
End
Close CursorCross
Deallocate CursorCross
if (@AggreGate is null) or @AggreGate=''
if @IntoTableName is null
Execute(' Select '+@vGroupByField+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)
-- print(' Select '+@vGroupByField+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)
else
Execute(' Select '+@vGroupByField+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' Into '+@IntoTableName+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)
else
if @IntoTableName is null
Execute(' Select '+@vGroupByField+','+@AggreGate+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)
-- Print(' Select '+@vGroupByField+','+@AggreGate+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)
else
Execute(' Select '+@vGroupByField+','+@AggreGate+' '+@StrSql+@StrSql2+@StrSql3+@StrSql4+@StrSql5+' Into '+@IntoTableName+' From '+@vSourceTAB+' '+@vStrWhere+' Group By '+@vGroupByField+' Order By '+@vGroupByField)

IF @@Error <>0 Return @@Error
Return 0
End
GO