Plot Pivot (Row in column)

Question

Plot Pivot (Row in column)

Asked 4 years, 7 months ago

Viewed 39 times

0

Gentlemen, good morning !

I am trying to make a simple parcel PIVOT under same customer number (330162210001), however I am not getting...

The idea is that each plot result found is a column.

We have tried various sql Aggregation Function, but to no avail.

You’re making this mistake :

We have tried to convert all possible type types so that it can read. But without success.

Type of Table we are working on:

Result of Subquery:

Code used:

SELECT * FROM (             
              SELECT dup.cliente, dup.VR_LIQ_ATU,dup.NUMERO, DUP.VALOR             
              FROM DUPLICATAS_ABERTAS DUP WHERE dup.CLIENTE = 330162210001) as Q 
PIVOT(     
       MAX (cliente)        
       FOR VR_LIQ_ATU IN ([VALOR_C]))AS PVT

If anyone can shed some light, we appreciate the understanding.

What would be this "VALOR_C"? I have not seen it present/declared in SQL. Another question is, if you have 1,000 rows, will it turn those 1,000 rows into 1,000 columns? Or there is a limit of returns per customer, for example, will have a maximum of 12 installments.

– Pedro Costa

2020/04/19 at 16:13
pedro o valor_c is just the column that he has to return the plot data. In this table there are up to 3 installments. So I don’t have to worry about that, you know? rs

– Maguim

2020/04/19 at 17:35
in fact the PIVOT, at least in SQL Server does not work like this, the field that is there in the IN is the identifier of the pivot column. I’ll put it in answer so you understand better.

– Pedro Costa

2020/04/19 at 22:16

1 answer

Browser other questions tagged sql-server tsql

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

by Pedro Costa • **503** points · Answer 1 · 2020-04-19T22:47:22+00:00

Maguim, as I mentioned in the comment, what is inside the IN is the identifier of the pivot column, for you to understand better, it must be the element that is in the line. By the data provided, I could assemble like this (there may be other ways, but I do not know how is the architecture of your bank in relation to the fields):

SELECT * 
FROM 
(SELECT 
    ROW_NUMBER() OVER(PARTITION BY CLIENTE ORDER BY CLIENTE) AS ORDEM,
    DUP.CLIENTE, 
    DUP.VR_LIQ_ATU, 
    DUP.NUMERO
    --DUP.VALOR 
    FROM TESTE DUP 
    --WHERE DUP.CLIENTE = 3301
) AS Q 
PIVOT(SUM(VR_LIQ_ATU) FOR ORDEM IN ([1], [2], [3]) )AS PVT

other scripts:

--CREATE TABLE TESTE (
--CLIENTE VARCHAR(100),
--VR_LIQ_ATU NUMERIC(10,4),
--NUMERO VARCHAR(100),
--VALOR VARCHAR(100)
--);

--INSERT INTO TESTE VALUES (3301, 173.55, 62, 'R$ 177,59' )
--INSERT INTO TESTE VALUES (3301, 1395.55, 62, 'R$ 1395,59' )
--INSERT INTO TESTE VALUES (3301, 1455.55, 62, 'R$ 1455,59' )
--INSERT INTO TESTE VALUES (3302, 152.55, 62, 'R$ 152,59' )
--INSERT INTO TESTE VALUES (3302, 11.55, 62, 'R$ 11,59' )
--INSERT INTO TESTE VALUES (3303, 1365.55, 62, 'R$ 1365,59' )
--INSERT INTO TESTE VALUES (3303, 188.55, 62, 'R$ 188,59' )