No pude encontrar una respuesta adecuada, así que la estoy escribiendo aquí. Tengo una tabla con los siguientes campos. ID Monto DocNum DocStatus DueDate AA 2400 00005 1 10-Jun -...

1
stackoverflow rohit 14 mar. 2021 a las 18:21

3 respuestas

La mejor respuesta

Puede usar row_number() y agregación condicional:

select id,
       max(case when seqnum = 1 then docnum end) as docnum_1,
       max(case when seqnum = 1 then amount end) as amount_1,
       max(case when seqnum = 2 then docnum end) as docnum_2,
       max(case when seqnum = 2 then amount end) as amount_2,
       max(case when seqnum = 3 then docnum end) as docnum_3,
       max(case when seqnum = 3 then amount end) as amount_3
from (select t.*,
             row_number() over (partition by id order by due_date desc) as seqnum
      from t
      where status = 1
     ) t
group by id;
1
Gordon Linoff 14 mar. 2021 a las 17:20
alter session set nls_date_format='dd-Mon-yyyy';

with
  my_table (id, amount, docnum, docstatus, duedate) as (
    select 'AA', 2400, '00005', 1, to_date('10-Jun-2019') from dual union all
    select 'AA', 1400, '00006', 4, to_date('21-Sep-2019') from dual union all
    select 'AA', 9000, '00028', 1, to_date('22-Aug-2020') from dual union all
    select 'AA', 5000, '00201', 2, to_date('31-Aug-2020') from dual union all
    select 'AA', 6400, '00410', 1, to_date('22-Jan-2021') from dual union all
    select 'AA', 2000, '00511', 1, to_date('01-Mar-2021') from dual union all
    select 'BB', 1500, '01390', 1, to_date('01-Jan-2021') from dual
  )
select id, "1_DOC" as document1, "1_AMT" as amount1,
           "2_DOC" as document2, "2_AMT" as amount2,
           "3_DOC" as document3, "3_AMT" as amount3
from   (
         select id, amount, docnum, 
                row_number() over (partition by id 
                                   order by duedate desc) as rn
         from   my_table
         where  docstatus = 1
       )
pivot  (min(docnum) as doc, min(amount) as amt for rn in (1, 2, 3))
;


ID DOCUMENT1    AMOUNT1 DOCUMENT2    AMOUNT2 DOCUMENT3    AMOUNT3
-- --------- ---------- --------- ---------- --------- ----------
AA 00511           2000 00410           6400 00028           9000
BB 01390           1500     

Debe hacer todo el trabajo de preparación en la subconsulta: filtre por docstatus = 1, cree la clasificación RN por duedate descendente y seleccione solo las columnas que necesita para el pivote. La consulta externa, que no sea el pivote trivial (trivial después de hacer todo el trabajo de preparación en la subconsulta), solo necesita un poco de cuidado en la cláusula select, para obtener los nombres de las columnas correctamente.

0
mathguy 14 mar. 2021 a las 15:49

Puede generar dinámicamente la instrucción SELECT de SQL deseada para pivotar las filas para mostrar si top 2,3,4 ..etc creando una función de este tipo con el parámetro IN para representar el top 2,3,4 ..etc y devolviendo { {X0}} tipo de resultado establecido como

CREATE OR REPLACE FUNCTION Fn_Pivot_Doc_and_Amounts( numcol INT ) RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG( ''||level||' AS "'||level||'"' , ',' )
                 WITHIN GROUP ( ORDER BY level )
    INTO v_cols
    FROM dual
   CONNECT BY level <= numcol;

  v_sql :='SELECT *
             FROM(SELECT id,docnum,amount,
                         ROW_NUMBER() OVER (PARTITION BY id ORDER BY duedate DESC) AS rn
                    FROM tab t
                   WHERE docstatus = 1)
            PIVOT(
                  MAX(docnum) AS document,
                  MAX(amount) AS amount  FOR rn IN ( '|| v_cols ||'  )
                 )';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

Y luego llame desde la consola de desarrollador de SQL como

SQL> DECLARE
       result SYS_REFCURSOR;
BEGIN
   :result := Fn_Pivot_Doc_and_Amounts(2); -- 3,4,...
END;
/

SQL> PRINT result;
0
Barbaros Özhan 14 mar. 2021 a las 16:31