how to pivot a column in a sub query withow temp table

• francotalamo

  Valued Member

  Points: 51

  More actions

  March 21, 2019 at 3:35 pm

  #383176

  Hi guys, I have this code :

  SELECT CC.PERIODO,
         CC.ID,
         CC.FACTURA,
         CC.FECHA,
         CC.MONTO,
         Q.FECHA_PAGO,
         Q.METODO,
         MIN(ABS(DATEDIFF(DAY,CC.FECHA, Q.FECHA_PAGO))) AS DIAS_PAGO
  FROM CuentasXCobrar CC
  LEFT JOIN (  SELECT PERIODO, 
                       ID, 
                        MONTO, 
                        FECHA, 'WEB' AS METODO         
                 FROM  Tablas Ventas_web         
                 UNION  PERIODO, 
                         ID, 
                         MONTO, 
                         FECHA, 
                         'ATC' AS METODO        
                 FROM Ventas_presenciales       
            ) Q ON CC.ID = Q.ID AND CC.PERIODO = Q.PERIODO

  This query return this :
  
  

  PERIODO	ID	FACTURA	FECHA	MONTO	PAGO	METODO	DIAS_PAGO
  201902	490	160175	14/02/2019	522,830	28/02/2019	WEB	14
  201902	490	160175	14/02/2019	954,190	28/02/2019	ATC	14
  201902	402	241288	21/02/2019	78,966	26/02/2019	ATC	5
  201902	411	165849	21/02/2019	415,966	26/02/2019	ATC	5
  201902	415	165041	18/02/2019	102,465	20/02/2019	ATC	2
  201902	415	165041	18/02/2019	200,985	19/02/2019	WEB	1

  
  
  I need to create a new  colum "MONTO"  in order to put de values of repeat ID, in this way

  PERIODO	ID	FACTURA	FECHA	MONTO	MONTO 2	PAGO	METODO	DIAS_PAGO
  201902	490	160175	14/02/2019	522,830	954,190	28/02/2019	WEB	14
  201902	402	241288	21/02/2019	78,966	0	26/02/2019	ATC	5
  201902	411	165849	21/02/2019	415,966	0	26/02/2019	ATC	5
  201902	415	165041	18/02/2019	102,465	200,985	19/02/2019	ATC	1

  
  I mean, I need to put the repeats records on another column and delete the record who has the highed "DIAS_PAGO" number.
  I will preciated.