Using pivot, in this query with dynamic columns

Asked

Viewed 77 times

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.

No answers

Browser other questions tagged

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