PIVOT - Difficulty with Columns

Asked

Viewed 59 times

0

Considering the table:

CREATE TABLE [dbo].[VENDAS](
    [VENCIMENTO] [datetime] NULL,
    [VENCIDO] [decimal](18, 0) NULL,
    [RECEBIDO] [decimal](18, 0) NULL
) ON [PRIMARY]

INSERT INTO VENDAS VALUES('2019-01-10', 50, 49,80)
INSERT INTO VENDAS VALUES('2019-02-10', 50, 49.80)
INSERT INTO VENDAS VALUES('2019-03-10', 50, 49,80)

How to achieve this result:

inserir a descrição da imagem aqui

I tried to come up with a result but to no avail. I used this consultation:

SELECT *
  FROM (SELECT VENCIDO, RECEBIDO, VENCIMENTO
          FROM VENDAS) AS V
PIVOT (SUM(V.RECEBIDO) FOR V.RECEBIDO IN ([0])) AS PV1

In case the Maturity records turn into columns.

sql-server

  • This is not pivot but matrix transposition. A solution is to mount 3 SELECT, one for each line of the result, and reunite them with UNION ALL.

  • 1

    Suggested reading: https://gustavomaiaaguiar.wordpress.com/2009/07/13/pivoteando-despivoteando-transpondo-invertendo-colunas-e-linhas-no-sql-server/

1 answer

2


Using the UNION ALL from our friend, I believe it can work. 

    SELECT  
         TIPO,
         [2019/01/10],
         [2019/02/10]
         [2019/03/10]
     FROM 
    (select 
          'VL_VENCIDO'as  TIPO,
           VENCIDO as  VALOR 
       from OPERACAO
    UNION ALL
    select 
          'VL_RECEBIDO'as  TIPO,
           RECEBIDO as  VALOR 
       from OPERACAO
     )AS TAB1

     PIVOT (SUM(vencido)
       FOR VENCIMENTO
        IN ( [2019/01/10],[2019/02/10],[2019/03/10])  
     ) P

Here I ended up creating a table by inserting the description VENCIDO and RECEBIDO and its values as DADOS.

Then it worked out in the structure below: 

SELECT CD,
       TIPO,
       [2019/05/01],
       [2019/05/02],
       [2019/05/03]
  FROM 

 ( SELECT tmp.cd,
          tmp.dt,
          TMPTIPO.TIPO,
          TMPTIPO.VALOR

     FROM TMP
     JOIN TMPTIPO ON tmp.cd_f = TMPTIPO.CD
               AND tmp.dt   = TMPTIPO.DT   
    WHERE tmp.dt BETWEEN '2019/05/01' AND '2019/05/30'
      AND TMP.cd=1

  ) AS S
  PIVOT (SUM(VALOR)
     FOR DT
      IN ( [2019/05/01],
           [2019/05/02],
           [2019/05/03])  
         ) P

Browser other questions tagged sql-server

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