isnull pivot server sql_使用SQL Server中的“Pivot”将行转换为列

我正在编写一个可能对此有用的sp，基本上这个sp会旋转任何表并返回一个新的表，或仅返回数据集，这是执行它的方法：

Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',

@sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;

请注意，在参数@agg中，列名必须为，'['参数必须以逗号结尾','

SP

Create Procedure [dbo].[rs_pivot_table]

@schema sysname=dbo,

@table sysname,

@column sysname,

@agg nvarchar(max),

@sel_cols varchar(max),

@new_table sysname,

@add_to_col_name sysname=null

As

--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';

Begin

Declare @query varchar(max)='';

Declare @aggDet varchar(100);

Declare @opp_agg varchar(5);

Declare @col_agg varchar(100);

Declare @pivot_col sysname;

Declare @query_col_pvt varchar(max)='';

Declare @full_query_pivot varchar(max)='';

Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica

Create Table #pvt_column(

pivot_col varchar(100)

);

Declare @column_agg table(

opp_agg varchar(5),

col_agg varchar(100)

);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))

Set @ind_tmpTbl=0;

ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL

Set @ind_tmpTbl=1;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR

OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL

Begin

Set @query='DROP TABLE '+@new_table+'';

Exec (@query);

End;

Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';

Print @query;

Insert into #pvt_column(pivot_col)

Exec (@query)

While charindex(',',@agg,1)>0

Begin

Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);

Insert Into @column_agg(opp_agg,col_agg)

Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));

Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))

End

Declare cur_agg cursor read_only forward_only local static for

Select

opp_agg,col_agg

from @column_agg;

Open cur_agg;

Fetch Next From cur_agg

Into @opp_agg,@col_agg;

While @@fetch_status=0

Begin

Declare cur_col cursor read_only forward_only local static for

Select

pivot_col

From #pvt_column;

Open cur_col;

Fetch Next From cur_col

Into @pivot_col;

While @@fetch_status=0

Begin

Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+

' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+

(case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'

print @query_col_pvt

Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '

--print @full_query_pivot

Fetch Next From cur_col

Into @pivot_col;

End

Close cur_col;

Deallocate cur_col;

Fetch Next From cur_agg

Into @opp_agg,@col_agg;

End

Close cur_agg;

Deallocate cur_agg;

Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);

Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+

@schema+'.'+@table+' Group by '+@sel_cols+';';

print @query;

Exec (@query);

End;

GO

这是一个执行的例子：

Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;

然后Select * From ##TEMPORAL1PVT会回来：