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

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

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