How to call sp and pass parameters to make pivot on sample below?

Question

Post reply

How to call sp and pass parameters to make pivot on sample below?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

January 7, 2021 at 4:19 pm

#3828606

I can't call sp and how to pass parameter to make pivot to temp table below

so can you help me execute this sp and get result

stored procedure name [dbo].[rs_pivot_table]

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
 CREATE TABLE #yt 
 (
   [Store] int, 
   [Week] int, 
   [xCount] int
 );
    
 INSERT INTO #yt
 (
   [Store], 
   [Week], [xCount]
 )
 VALUES
     (102, 1, 96),
     (101, 1, 138),
     (105, 1, 37),
     (109, 1, 59),
     (101, 2, 282),
     (102, 2, 212),
     (105, 2, 78),
     (109, 2, 97),
     (105, 3, 60),
     (102, 3, 123),
     (101, 3, 220),
     (109, 3, 87);

Expected result as below

 Store        1          2          3        4        5        6....
 ----- 
 101        138        282        220
 102         96        212        123
 105         37        
 109

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

You can insert the results of a stored procedure into a temporary table pretty easily

SELECT *
INTO #TempResult
FROM
(SELECT store, [week], xCount
FROM yt) AS sourceTable
PIVOT 
(
SUM(xCount)
FOR [week] in ([1],[2],[3],[4],[5],[6])
) AS PivotTable;

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply

procedure above make pivot on general

so an you please help me how to execute it

exec [dbo].[rs_pivot_table] ?,???????

what paramters i pass to work

this is exactly my question

pietlinden SSC Guru Points: 63506 More actions · Answer 3

Did you read this yet?

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns – SQLServerCentral