https://ithelp.ithome.com.tw/articles/10340771
https://hackmd.io/@pgsql-tw/B13CK7oVp
https://ithelp.ithome.com.tw/articles/10340780

Oracle 有 Json_Table 這個超強函數.

也可以用PostgreSQL 的 Oracle_FDW 連到Oracle, 用PG產生報表.
做了一點測試:
https://dbfiddle.uk/Yxn-JyU2

CREATE TABLE main
(
  morderno varchar(2),
  mgroup varchar(2),
  mseq numeric(1),
  mitem varchar(3),
  mamt numeric(2),
  munitprice numeric(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)
;

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

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

SELECT * FROM MAIN;

DO LANGUAGE plpgsql $$
DECLARE v_sqlstring VARCHAR  = '';
BEGIN
v_sqlstring := concat('create temp table tmpxt231220e as select null::text as mgroup, ',
                 (select string_agg(concat('NULL::text AS ', 'p', n::text, '_', ch), ' ,'
                        order by n)
                  from (values ('mitem'),('mamt'),('munitprice')) as a(ch)
                     , generate_series(1,5) as n)::text);

EXECUTE(v_sqlstring);
END $$;


with t1(js1) as (
select jsonb_build_object('mgroup', mgroup)
    || jsonb_object_agg(lower(morderno) || '_mitem', mitem)
    || jsonb_object_agg(lower(morderno) || '_mamt', mamt)
    ||jsonb_object_agg(lower(morderno) || '_munitprice', munitprice)
  from main
 group by mgroup, mitem
)
select (jsonb_populate_recordset(null::tmpxt231220e, jsonb_build_array(js1))).*
  from t1
 order by mgroup;

幫海棉大大圖解XD...

看樣子~閒閒寫T-SQL來解決@@...雖然不是oracle

CREATE TABLE main(
  morderno varchar(2),
  mgroup varchar(2),
  mseq int,
  mitem varchar(3),
  mamt int,
  munitprice int
)

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)

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

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

Declare @StrTital1 NVARCHAR(Max) =''
Declare @StrTital2 NVARCHAR(Max) =''
Declare @StrTital3 NVARCHAR(Max) =''
Declare @StrTital4 NVARCHAR(Max) =''
Declare @StrTital5 NVARCHAR(Max) =''
Declare @StrTital6 NVARCHAR(Max) =''
Declare @StrTital7 NVARCHAR(Max) =''
Declare @StrSQL NVARCHAR(Max) =''


Select @StrTital1= @StrTital1+QUOTENAME(morderno+' item')+N',' From main Group By morderno
Select @StrTital2= @StrTital2+QUOTENAME(morderno+' amt')+N',' From main Group By morderno
Select @StrTital3= @StrTital3+QUOTENAME(morderno+' unitprice')+N',' From main Group By morderno
Select @StrTital4= @StrTital4+('max(['+morderno+' item]) ['+morderno+' item]')+','+('max(['+morderno+' amt]) ['+morderno+' amt]')+','+('max(['+morderno+' unitprice]) ['+morderno+' unitprice]')+',' From main Group By morderno
Select @StrTital5= @StrTital5+('['+morderno+' item]')+','+(''''' ['+morderno+' amt]')+','+(''''' ['+morderno+' unitprice]')+',' From main Group By morderno
Select @StrTital6= @StrTital6+(''''' ['+morderno+' item]')+','+('['+morderno+' amt]')+','+(''''' ['+morderno+' unitprice]')+',' From main Group By morderno
Select @StrTital7= @StrTital7+(''''' ['+morderno+' item]')+','+(''''' ['+morderno+' amt]')+','+('['+morderno+' unitprice]')+',' From main Group By morderno
Set @StrTital1=  Left(@StrTital1, Len(@StrTital1)-1) 
Set @StrTital2=  Left(@StrTital2, Len(@StrTital2)-1) 
Set @StrTital3=  Left(@StrTital3, Len(@StrTital3)-1) 
Set @StrTital4=  Left(@StrTital4, Len(@StrTital4)-1) 
Set @StrTital5=  Left(@StrTital5, Len(@StrTital5)-1) 
Set @StrTital6=  Left(@StrTital6, Len(@StrTital6)-1) 
Set @StrTital7=  Left(@StrTital7, Len(@StrTital7)-1) 

Set @StrSQL= N'
	select mgroup
	,'+@StrTital4+'
	from ((
		Select mgroup
		,mgroupnum
		,'+@StrTital5+'
		From
		(
			Select mgroup
			,morderno + '' item'' morderno
			,mgroup+Convert(varchar,mseq) mgroupnum
			,mitem 
			From main
		) As a1
		pivot
		(
			max(mitem) 
			For morderno IN ('+@StrTital1+N')
		) As a2
	)union all(
		Select mgroup
		,mgroupnum
		,'+@StrTital6+'
		From
		(
			Select mgroup
			,morderno + '' amt'' morderno
			,mgroup+Convert(varchar,mseq) mgroupnum
			,mamt 
			From main
		) As a1
		pivot
		(
			max(mamt) 
			For morderno IN ('+@StrTital2+N')
		) As a2
	)union all(
		Select mgroup
		,mgroupnum
		,'+@StrTital7+'
		From
		(
			Select mgroup
			,morderno + '' unitprice'' morderno
			,mgroup+Convert(varchar,mseq) mgroupnum
			,munitprice 
			From main
		) As a1
		pivot
		(
			max(munitprice) 
			For morderno IN ('+@StrTital3+N')
		) As a2
	)) k
	group by mgroup
	,mgroupnum
'
--print @StrSQL

EXEC sp_executesql @StrSQL 

go

Drop Table main

查了一下適用Oracle的T-SQL，叫做PL/SQL
https://www.oracle.com/tw/database/technologies/appdev/plsql.html

可惜電腦沒裝Oracle，不然把T-SQL改寫成PL/SQL應該沒問題的^_^

Oracle Row to Column 函式介紹

我都是用tableau直接拉表的.