PIVOT SQL - Dynamic Columns with Variable

Asked

Viewed 1,253 times

1

I have a table which I turn rows into columns with PIVOT. 'Chumbando' the name of the columns, works very well, however I will need to do this dynamically. There is the possibility to use variables to define the column names?

        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 four months are dynamic and informed in the Store Procedure call. SP creates the #VENDASESTM table correctly with products, chosen months and quantities sold. Then I need to transform it with PIVOT also dynamically.

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

sql-server pivot

  • If you do select by changing the month variables (exe; @MES3), for a fixed value (exe; 3) it works ?

  • Yes @Marconcíliosouza [05],[06],[07] and chumbados etc works perfectly. But now I need the columns to be determined by variable return

  • Which database and type of ISSUE column :?

  • SQL Database and ISSUANCE as Character 2

  • SQL, is not database, your Character 2 you speak varchar(2)? the database is sql server?

  • I just forgot the rest of the SQL name, rs. SQL SERVER 2008 I just switched to Varchar(2) to do a test, before I was just like Char... is processing the query (it’s kind of heavy and the server is slow). Let’s see the result.

  • Gave anyway, returned everything zeroed

  • your @MES3 parameter is also of the same type?

  • Yes, the four months are the same

Show 4 more comments

1 answer

1


In order to be able to do your PIVOT by passing parameters you will have to pass your select to a varchar variable and then use 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 only the section below. 

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

Browser other questions tagged sql-server pivot

You are not signed in. Login or sign up in order to post.