Oracle SQL to convert rows to columns

Question

I could not find a suitable answer so I am writing it here. I have a table with following fields.

ID           Amount    DocNum   DocStatus   DueDate
AA           2400      00005     1          10-Jun-2019
AA           1400      00006     4          21-Sep-2019
AA           9000      00028     1          22-Aug-2020 
AA           5000      00201     2          31-Aug-2020
AA           6400      00410     1          22-Jan-2021
AA           2000      00511     1          01-Mar-2021
BB           1500      01390     1          01-Jan-2021

I would want to display a top 3 latest documents with Status 1

ID Document1 Amount1 Document2 Amount2 Document3 Amount3 
AA 00511     2000    00410     6400    00028     9000 
BB 01390     1500    XX        XX      XX        XX

I thought I could use Pivot or Decode but unable to determine other conditions. Any help is appreciated.

Gordon Linoff · Accepted Answer · 2021-03-14 17:20:58Z

2

You can use row_number() and conditional aggregation:

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;

edited Mar 14, 2021 at 17:20

answered Mar 14, 2021 at 15:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2021-03-14 15:49:32Z

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

You need to do all the prep work in the subquery: filter for docstatus = 1, create the RN ranking by duedate descending, and select just the columns you need for the pivot. The outer query, other than the trivial pivoting (trivial after you do all the prep work in the subquery), needs just a bit of care in the select clause, to get the column names right.

Barbaros Özhan · Accepted Answer · 2021-03-14 16:31:06Z

You can dynamically generate the desired SQL SELECT Statement in order to pivot the rows to display whether top 2,3,4 ..etc by creating such a function with IN parameter to represent the top 2,3,4 ..etc and returning SYS_REFCURSOR type result set as

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;

and then call from SQL Developer's console as

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

SQL> PRINT result;

Collectives™ on Stack Overflow

Oracle SQL to convert rows to columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related