Inner Join without duplicating lines, bring the first value found

Question

I have the following table with the fields below

SELECT NR_ANO_MES, nr_contract, type_product, NM_PRODUTO, sum(QT) the QT

FROM INN.FT_VENDA

The product type field has 3 classifications (Video, data, voice) and the product name field has a multitude. a single contract may have the 3 product types.

I want to bring in a line only for each contract with the 3 types of products that each contract can have and their respective product names, that is, the product type field will be transformed into 3 columns each with the name of their respective products. So far so good, the problem is that a contract can have more than one type of "video" product there when I do the Inner Join it ends up doubling the line of the contract to bring the two video products. I wanted to bring only one line with only one of the product names videos, whatever name bring. someone has a light?

Answer 1

Try using the PIVOT clause, for example: https://www.techonthenet.com/oracle/pivot.php

In practice it will be something like this:

SELECT *
FROM
(
 SELECT NR_ANO_MES, nr_contrato,NM_PRODUTO
 FROM INN.FT_VENDA 
)
PIVOT
(
    COUNT(tipo_produto),
    FOR(tipo_produto) IN ('Video', 'dados', 'voz')
)