1
I have the query below where I display products, ordered by the product code, the date of movement of the product is from the latest to the oldest, but as I wanted only the last 5 dates of a defined period, I used rank, so far so good. But now he wanted to turn the date into column and put his respective Number. of drive under it. However it is dynamic, I can have from 1 to 5 columns. and this row of columns according to the product Cod.. ex. first row of columns are the 5 dates of product 1, the next row of 5 columns below the previous one, the dates of product 2 and so on.
SELECT *
FROM
(
SELECT
TB2.CD_CLASSE
,TB2.CD_PRODUTO
,TB2.DS_PRODUTO
,TB2.DH_MVTO_ESTOQUE
,TB2.QT_MOVIMENTACAO
,TB2.QT_ESTOQUE_ATUAL
,TB2.TP_CLASSIFICACAO_XYZ
,TB2.TP_CLASSIFICACAO_ABC,
Dense_Rank () OVER (
PARTITION BY TB2.CD_PRODUTO
ORDER BY TB2.DH_MVTO_ESTOQUE DESC
) AS RANK_PROD
FROM
(
SELECT DISTINCT
TB1.CD_CLASSE
,TB1.CD_PRODUTO
,TB1.DS_PRODUTO
,MVT_EST.DH_MVTO_ESTOQUE
,MVT_EST.QT_MOVIMENTACAO
,TB1.QT_ESTOQUE_ATUAL
,TB1.TP_CLASSIFICACAO_XYZ
,TB1.TP_CLASSIFICACAO_ABC
FROM
ITMVTO_ESTOQUE MVT_EST,
(
SELECT DISTINCT
PROD.CD_CLASSE
,PROD.CD_PRODUTO
,PROD.DS_PRODUTO
,PROD.QT_ESTOQUE_ATUAL
,EMPROD.TP_CLASSIFICACAO_XYZ
,EMPROD.TP_CLASSIFICACAO_ABC
FROM
PRODUTO PROD
,EMPRESA_PRODUTO EMPROD
WHERE
PROD.CD_PRODUTO = EMPROD.CD_PRODUTO
ORDER BY PROD.CD_PRODUTO
)TB1
WHERE
TB1.CD_PRODUTO = MVT_EST.CD_PRODUTO AND
MVT_EST.DH_MVTO_ESTOQUE BETWEEN TO_DATE ('20/05/2020', 'dd/mm/yyyy')
AND TO_DATE ('20/07/2020', 'dd/mm/yyyy')
)TB2
)
WHERE RANK_PROD <= 5
I’ve never seen a practical way to do this, I’ll see if anyone gives any idea.
– Motta