SQLTeam.com | Weblogs | Forums

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

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

The procedure you wrote is very confusing. If you are looking for a generic procedure to pivot data on, you can try this, although I believe using the case when and sum method is faster. This uses the sample data you provided.

DDL

Summary

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);

PROC

Summary

Create Procedure [dbo].[rs_pivot_table]
@schema sysname=dbo,
@table sysname,
@column sysname, -- this is the column we want to pivot the values on
@agg nvarchar(max), -- this is the column we want to aggregate for the pivot columns
@sel_cols varchar(max), -- this is the columns we want to include in the result (must include pivot and agg column)
@new_table sysname, -- this is the new table we want to pipe the values into - NOT IMPLEMENTED HERE
@add_to_col_name sysname=null -- no idea why this is here

as 	 

--exec [dbo].[rs_pivot_table]
--    @schema ='dbo',
--     @table  = '#yt',
--     @column = 'Week', 
--     @agg  = 'Sum(xCount)',
--     @sel_cols  = 'Store, xCount, [Week]',
--     @new_table = null,
--     @add_to_col_name =null

 Begin
    
     Declare @query varchar(max)='',
	         @TableName sysname,
			 @Colslist VARCHAR(MAX)  
	IF OBJECT_ID('tempdb..#pvt_column') IS NOT NULL 
	    DROP TABLE #pvt_column

     Create Table #pvt_column(
         pivot_col varchar(100)
     );
    
     IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
		begin
			Select @query='Select distinct '+@column+' From '+ @schema+'.'+@table+' where '+@column+' is not null;',
				   @TableName =  @schema+'.'+@table;
		end
     ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
		begin
			Select @query='Select distinct '+@column+' From '+ @table+' where '+@column+' is not null;',
				   @TableName = @table;
		end
  	else
	begin
		set @TableName = @Table + ' does not exist'
		;Throw 51000, @TableName, 1
		return
	end

	 -- get list of distinct values to pivot
    Insert into #pvt_column(pivot_col)
    Exec (@query)
    
	SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + pivot_col + ']'  
	FROM #pvt_column t  
	 
	EXEC ('SELECT *  
	FROM   
	(  
	        SELECT ' + @sel_cols + ' 
			  FROM ' +  @TableName + '
			) t  
			PIVOT (' + @agg + ' FOR [' + @Column + '] IN (' + @ColsList + ')) PVT')  

 End;
 GO

Execution

Summary

exec [dbo].[rs_pivot_table]
@schema ='dbo',
@table = '#yt',
@column = 'Week',
@agg = 'Sum(xCount)',
@sel_cols = 'Store, xCount, [Week]',
@new_table = null,
@add_to_col_name =null

thank you for reply
i use following stored procedure below with your modification on answer
exec [dbo].[rs_pivot_table] 'dbo','#yt','Week', 'Sum(xCount)','Store, xCount, [Week]', null,null
but i get error as below
(0 row(s) affected)
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

can any one help me please

sorry it working success
only i also need to add
avg(xcount) meaning i need to make multiple pivot
so how to do that
i try as below
exec [dbo].[rs_pivot_table]
@schema ='dbo',
@table = '#yt',
@column = 'Week',
@agg = 'Sum(xCount),avg(xCount)',
@sel_cols = 'Store, xCount, [Week]',
@new_table = null,
@add_to_col_name =null
but i get
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near ','.

so please how to solve issue

there are 2 things:

  1. the proc failed because the table did not exist. I modified DDL to include check for this
  2. I'm not sure what your requirements are. It seemed you were looking for a generic proc to pivot data on. That is what I gave you. If you need to use average instead of sum, then call the proc like this:

exec [dbo].[rs_pivot_table]
@schema ='dbo',
@table = '#yt',
@column = 'Week',
@agg = 'AVG(xCount)',
@sel_cols = 'Store, xCount, [Week]',
@new_table = null,
@add_to_col_name =null

If you need something more complex, like pivoting 2 values, this is not the proc for that.

for pivot sum only it working
but if i need sum and avg both on two columns
so this is actually my question
for pivot one colum it is ok
but multiple pivot not working
sum(xCount),avg(xCount)

That can be done with a query. Here is the sql that you can start with.

DECLARE @SumColslist VARCHAR(MAX)  = '',
		 @AvgColslist VARCHAR(MAX)  = '',
		 @SumMaxColslist VARCHAR(MAX)  = '',
		 @AvgMaxColslist VARCHAR(MAX)  = '',
		 @sql nvarchar(max),
		 @e nvarchar(500)
 
 Declare @Cols TABLE (Head VARCHAR(MAX))  
   
 INSERT @Cols (Head)  
 SELECT DISTINCT [Week]  
 FROM #yt
  
 SELECT @SumMaxColslist = COALESCE(@SumMaxColslist + ',max([S', 'max([S') + Head + ']) as SumWeek'  + Head ,
	    @AvgMaxColslist = COALESCE(@AvgMaxColslist + ',max([A', 'max([A') + Head + ']) as AvgWeek'  + Head   ,
		@SumColslist = COALESCE(@SumColslist + ',[S', '[S') + Head + ']' ,
	    @AvgColslist = COALESCE(@AvgColslist + ',[A', '[A') + Head + ']'  
 FROM @Cols t  
	 
	set @sql = 'SELECT Store ' + @SumMaxColslist + @AvgMaxColslist + '
	FROM   
	(  
	        SELECT Store, Xcount as SumXcount, Xcount as AvgXcount, ''S'' + cast([Week] as varchar(10)) as SumWeek, ''A'' + cast([Week] as varchar(10)) as AvgWeek
			  FROM #yt
			) t  
			PIVOT (sum([SumXcount]) FOR [SumWeek] IN (' + right(@SumColslist, len(@SumColslist) - 1) + ')) PVT 
			PIVOT (avg([AvgXcount]) FOR [AvgWeek] IN (' + right(@AvgColslist, len(@AvgColslist) - 1) + ')) APVT 
		group by Store	'

    begin try
		exec(@SQL)
	end try
	begin catch
		Print (@SQL)
		set @e = Error_Message()
		;throw 51000, @e, 1
	end catch

thank you very much
solved