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

Hello Ahmed ,

In below query i try to make table as yours and perform pivot table for your desire output , you can implement this query in your sp as you wish . I Hope this helps you.

Data in table

SQL Code

Declare @ColumnName nvarchar(max)
Declare @SQLQuery nvarchar(max)
select @ColumnName=STUFF((select distinct ',['+ cast( Week as nvarchar)+']' from StoreTbl for xml path ('') ),1,1,'')
print @ColumnName
set @SQLQuery= 'select Store,'+@ColumnName+' from (
select * from StoreTbl )
ST
PIVOT (
SUM(xCount) for Week in ('+@ColumnName+')
)as StorePivot'
print @SQLQuery
EXEC SP_EXECUTESQL @SQLQuery

Output

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Yogeshkumar Hadiya5y ago
Hello Ahmed ,
In below query i try to make table as yours and perform pivot table for your desire output , you can implement this query in your sp as you wish . I Hope this helps you.

Data in table

SQL Code
Declare @ColumnName nvarchar(max)
Declare @SQLQuery nvarchar(max)
select @ColumnName=STUFF((select distinct ',['+  cast( Week as nvarchar)+']' from StoreTbl for xml path ('') ),1,1,'')
print @ColumnName
set @SQLQuery= 'select Store,'+@ColumnName+' from (
                select * from StoreTbl )
                ST
                PIVOT (
                        SUM(xCount) for Week in ('+@ColumnName+')
                )as StorePivot'
print @SQLQuery
EXEC SP_EXECUTESQL @SQLQuery
Output
Nikunj Satasiya5y ago
Hi Ahmed, You can visit this article where all the things are explained in detail level, which may help you to resolve your query.
Article: https://www.codingvila.com/2019/05/pivot-and-unpivot-in-sql-server-with-example.html
ahmed elbarbary5y ago
thank you for reply
can you please help me nswering question by code
if possible
Mageshwaran R5y ago
https://stackoverflow.com/questions/30874067/passing-a-parameter-into-a-sql-pivot