Inner join without duplicate rows, bring the first value I find

Navigation
0

I have the following table with the fields below

SELECT NR_ANO_MES, nr_contract, product_type, NM_PRODUCT, sum (QT) as QT

FROM   INN.FT_VENDA

The product_type field has 3 classifications (Video, Data, Voice) and the Product Name field has an infinity. the same contract can have all 3 product types.

I want to bring in a single line 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 kind of video product, so when I do the inner join it ends up doubling the contract line to bring the two video products. I wanted to bring only one line with just one of the product video names, whatever name it brings. Does anyone have a light?

    
asked by anonymous 14.11.2017 / 19:43

1 answer

0

Try using the PIVOT clause, for example: link

In practice it will look 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')
)
    
15.11.2017 / 18:04
jQuery smoth scrolling is not working My wamp does not work