PIVOT SQL - Dynamic Columns with Variable

Question

PIVOT SQL - Dynamic Columns with Variable

Navigation

#1 by (1 votes)

1

I have a table which I convert lines into columns with PIVOT. 'Chumbando' the column name works very well, however I will need to do this dynamically. Is it possible to use variables to define the name of the columns?

        SELECT PRODUTO,
           ISNULL([@MES3],0) AS MES3,
           ISNULL([@MES2],0) AS MES2,
           ISNULL([@MES1],0) AS MES1,
           ISNULL([@MES0],0) AS MESATUAL
        FROM #VENDASESTM 
             PIVOT (SUM(QTDVEND) FOR EMISSAO IN ([@MES3],[@MES2],[@MES1],[@MES0]) )P
        ORDER BY PRODUTO

These 4 months are dynamic and informed in the Store Procedure call. SP creates the #VENDASESTM table correctly with the products, months chosen and quantities sold. Then I need to transform this with PIVOT as well dynamically.

As above, the result was 0 in all columns. I did the conference and there is sales, so something is wrong.

sql-server pivot

asked by anonymous 03.08.2016 / 17:26

1 answer

Error creating database with sqlite [closed] Error generating pdf report (Jasper)

score 1 · Accepted Answer

To get your PIVOT passing parameters you have to pass your select to a varchar variable and then use the execute to execute the query as follows.

declare @MES2 varchar(2) = '02', @MES3 varchar(2) = '03', @MES1 varchar(2) ='01', @MES0 varchar(2) ='00' 
DECLARE @query  AS NVARCHAR(MAX)

set @query = '
declare @VENDASESTM table
(
   PRODUTO int,
   QTDVEND int,
   EMISSAO varchar(2)
)


insert into @VENDASESTM values
(1,1,''01''),
(2,1,''02''),
(3,1,''03''),
(3,1,''00''),
(4,1,''04''),
(2,1,''02'')

SELECT PRODUTO, 
        ISNULL([' +@MES3 +'],0) AS MES3,
        ISNULL([' +@MES2+'],0) AS MES2,
        ISNULL([' +@MES1+'],0) AS MES1,
        ISNULL([' +@MES0+'],0) AS MESATUAL
        FROM @VENDASESTM vc
             PIVOT  (SUM(QTDVEND) FOR EMISSAO IN ([' +@MES3+'],[' +@MES2+'],[' +@MES1+'],[' +@MES0+']) )P
        ORDER BY PRODUTO';

execute(@query);

In your case, just the following.

set @query = '
SELECT PRODUTO, 
        ISNULL([' +@MES3 +'],0) AS MES3,
        ISNULL([' +@MES2+'],0) AS MES2,
        ISNULL([' +@MES1+'],0) AS MES1,
        ISNULL([' +@MES0+'],0) AS MESATUAL
        FROM @VENDASESTM vc
             PIVOT  (SUM(QTDVEND) FOR EMISSAO IN ([' +@MES3+'],[' +@MES2+'],[' +@MES1+'],[' +@MES0+']) )P
        ORDER BY PRODUTO';

execute(@query);