Oracle group multiple rows into one base on one column's value

Question

Currently, I have 1 table with a structure like below (The ID column is the primary key):

ID                   Sub_ID    ACC_TYPE     ACC_NO    ACC_CCY    AMOUNT
----------------     ------    ----------   ------    --------   ------
KA31005493FC27XQ     DEF       MAIN_CR      1111      USD        100
KA31005493FC27XR     DEF       MAIN_DR      2222      EUR        200
KA31005493BC27XP     DEF       SA_MAIN_CR   3333      JPY        300
KA310054937C27XO     DEF       SA_MAIN_DR   4444      HKD        400
KA31005493KC27XZ     DEF       PNL_CR       5555      CNY        500

The values above do not have any rules, I just made it up for you to understand easily.
I want to select ACC_NO, ACC_CCY column of rows which has ACC_TYPE = MAIN_CR/ SA_MAIN_DR and put it to 1 row with columns name separated. Also with rows that have ACC_TYPE = SA_MAIN_CR/ MAIN_DR to 1 row.
And the AMOUNT_CR/ AMOUNT_DR column equal to AMOUNT of rows with the corresponding ACC_TYPE (MAIN_CR with SA_MAIN_CR to AMOUNT_CR, MAIN_DR with SA_MAIN_DR to AMOUNT_DR column).
Below is my expected output:

Sub_ID    ACC_NO_CR      ACC_CCY_CR    ACC_NO_DR    ACC_CCY_DR    AMOUNT_CR    AMOUNT_DR
------    ----------     ----------    ---------    ----------    ---------    ---------
DEF       1111           USD           4444         HKD           100          400
DEF       3333           JPY           2222         EUR           300          200

In case there have not been SA_MAIN_CR/ SA_MAIN_DR rows, the output should be:

Sub_ID    ACC_NO_CR      ACC_CCY_CR    ACC_NO_DR    ACC_CCY_DR    AMOUNT_CR    AMOUNT_DR
------    ----------     ----------    ---------    ----------    ---------    ---------
DEF       1111           USD           2222         EUR           100          200

Answer 1

Sure it is possible.

To create the table of your data:

create table tab as
with inputs (ID,Sub_ID,ACC_TYPE,ACC_NO,ACC_CCY,AMOUNT) as
(
    select 1,'DEF','MAIN_CR',1111,'USD',100 from dual union all
    select 2,'DEF','MAIN_DR',2222,'EUR',200 from dual union all
    select 3,'DEF','SA_MAIN_CR',3333,'JPY',300 from dual union all
    select 4,'DEF','SA_MAIN_DR',4444,'HKD',400 from dual
)
select * from inputs;

Solution:

select  sub_id_general as sub_id, acc_no_cr, acc_ccy_cr, amount_cr, acc_no_dr, acc_ccy_dr, amount_dr 
from    tab
match_recognize
(
    order by id
    measures sub_id as sub_id_general,
             CR.acc_no as acc_no_cr,
             CR.acc_ccy as acc_ccy_cr,
             CR.amount as amount_cr,
             DR.acc_no as acc_no_dr,
             DR.acc_ccy as acc_ccy_dr,
             DR.amount as amount_dr
    pattern (CR DR)
    define  CR as acc_type in ('MAIN_CR','SA_MAIN_CR'),
            DR as acc_type in ('MAIN_DR','SA_MAIN_DR')
);

Output: