Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE main
(
  morderno varchar(2),
  mgroup varchar(2),
  mseq number(1),
  mitem varchar(3),
  mamt number(2),
  munitprice number(5)
)
insert into main values
 ('P1','GA',1,'A01',11,111)
,('P1','GA',2,'A02',12,112)
,('P1','GB',1,'B01',13,113)
,('P1','GB',2,'B02',14,114)
,('P1','GC',1,'C01',15,115)
,('P1','GC',2,'C02',16,116)
  ;
6 rows affected

insert into main values 
 ('P2','GA',1,'A01',21,221)
,('P2','GB',1,'B01',22,222)
,('P2','GC',1,'C01',23,223)
;
3 rows affected

insert into main values 
 ('P3','GB',2,'B02',31,331)
,('P3','GC',2,'C02',32,332)
;
2 rows affected

create global temporary table tmpxt231220e as select 'null' as mgroup, 'null' AS p1_mitem ,'null' AS p1_mamt ,'null' AS p1_munitprice ,'null' AS p2_mitem ,'null' AS p2_mamt ,'null' AS p2_munitprice ,'null' AS p3_mitem ,'null' AS p3_mamt ,'null' AS p3_munitprice ,'null' AS p4_mitem ,'null' AS p4_mamt ,'null' AS p4_munitprice ,'null' AS p5_mitem ,'null' AS p5_mamt ,'null' AS p5_munitprice
1 rows affected

WITH t1 AS (
    SELECT JSON_OBJECT(
               'mgroup' VALUE mgroup,
               lower(morderno) || '_mitem' VALUE JSON_ARRAYAGG(mitem),
               lower(morderno) || '_mamt' VALUE JSON_ARRAYAGG(mamt),
               lower(morderno) || '_munitprice' VALUE JSON_ARRAYAGG(munitprice)
           ) AS js1,
           mgroup  -- Add mgroup to the group by
    FROM main
    GROUP BY mgroup, lower(morderno)  -- Include lower(morderno) in the group by
)
SELECT *
FROM JSON_TABLE(
         (SELECT JSON_ARRAYAGG(js1) AS json_array FROM t1),
         '$[*]'
         COLUMNS (
             mgroup VARCHAR2(100) PATH '$.mgroup',
             mitem VARCHAR2(100) FORMAT JSON PATH '$.*."_mitem"',
             mamt VARCHAR2(100) FORMAT JSON PATH '$.*."_mamt"',
             munitprice VARCHAR2(100) FORMAT JSON PATH '$.*."_munitprice"'
         )
     ) AS tmpxt231220e
ORDER BY mgroup;


MGROUP
MITEM
MAMT
MUNITPRICE


GA
null
null
null

GA
null
null
null

GB
null
null
null

GB
null
null
null

GB
null
null
null

GC
null
null
null

GC
null
null
null

GC
null
null
null

MGROUP	MITEM	MAMT	MUNITPRICE
GA	null	null	null
GA	null	null	null
GB	null	null	null
GB	null	null	null
GB	null	null	null
GC	null	null	null
GC	null	null	null
GC	null	null	null