Pivot em PL/SQL
- #Oracle DB
- #Oracle
/*******RETORNA TOTAL EM QUANTIDADE DOS PRODUTOS VENDIDOS DE TODO ANO 2023 *************************/
SELECT *
FROM
(
SELECT
D.CORPORATENAME AS DISTRIBUIDOR,P.description, SUM(II.quantity) AS 'TOTAL',
--REPLACE(CAST(ROUND(VII.ITEMAMOUNT * VII.QUANTITY, 2) AS VARCHAR), '.', ',') AS MT_VENDAS,
CASE
WHEN I.INVOICEDATE BETWEEN '2023-01-01' AND '2023-01-31' THEN 'ENERO'
WHEN I.INVOICEDATE BETWEEN '2023-02-01' AND '2023-02-28' THEN 'FEBRERO'
WHEN I.INVOICEDATE BETWEEN '2023-03-01' AND '2023-03-31' THEN 'MARZO'
WHEN I.INVOICEDATE BETWEEN '2023-04-01' AND '2023-04-30' THEN 'ABRIL'
WHEN I.INVOICEDATE BETWEEN '2023-05-01' AND '2023-05-31' THEN 'MAYO'
WHEN I.INVOICEDATE BETWEEN '2023-06-01' AND '2023-06-30' THEN 'JUNIO'
WHEN I.INVOICEDATE BETWEEN '2023-07-01' AND '2023-07-31' THEN 'JULIO'
WHEN I.INVOICEDATE BETWEEN '2023-08-01' AND '2023-08-31' THEN 'AGOSTO'
WHEN I.INVOICEDATE BETWEEN '2023-09-01' AND '2023-09-30' THEN 'SETIEMBRE'
WHEN I.INVOICEDATE BETWEEN '2023-10-01' AND '2023-10-31' THEN 'OCTUBRE'
WHEN I.INVOICEDATE BETWEEN '2023-11-01' AND '2023-11-30' THEN 'NOVIEMBRE'
WHEN I.INVOICEDATE BETWEEN '2023-12-01' AND '2023-12-31' THEN 'DICIEMBRE'
END AS MESES
FROM DEALER D
INNER JOIN VINVOICE I ON
D.DOCUMENTNUMBER = I.dealerSiteDocument
INNER JOIN VINVOICETYPE IT ON
I.invoiceType = IT.dealerCode
INNER JOIN VINVOICEITEM II ON
I.dealerCode = II.invoice
INNER JOIN VPRODUCT P ON
II.product = P.dealerCode
WHERE
I.INVOICEDATE BETWEEN '2023-01-01' AND '2023-08-31'
--and P.description LIKE ('%ANTRACOL KILO%')
GROUP BY
D.CORPORATENAME,I.INVOICEDATE,
P.description,
II.quantity
) DATOS_PIVOT
PIVOT
(SUM(TOTAL)
FOR MESES IN ("ENERO", "FEBRERO", "MARZO", "ABRIL", "MAYO", "JUNIO", "JULIO", "AGOSTO",
"SETIEMBRE", "OCTUBRE","NOVIEMBRE", "DICIEMBRE")
) RESULT_PIVOT
ORDER BY DISTRIBUIDOR
