Postgres 14

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

create table table_1(Order_Id int, Order_line_id int) ;
insert into table_1 values
(1,	1001),
(1,	1),
(1,	2);

create table table_2 (Order_Id int, Order_line_id int, Type text, Amount int) ;
insert into table_2 values
(1,	1001,	'APPLE',	60),
(1,	1001,	'APPLE',	90),
(1,	1,	'APPLE',	0),
(1,	1,	'ORANGE',	32),
(1,	1,	'KIWI',	45),
(1,	2,	'APPLE',	12),
(1,	2,	'ORANGE',	76),
(1,	2,	'ORANGE',	98);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 8


CREATE OR REPLACE PROCEDURE composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
SELECT string_agg(m.name || ' text', ', ')
  INTO column_list
  FROM
     ( SELECT Type || generate_series(1, max(count)) AS name
         FROM
            ( SELECT lower(Type) AS type, count(*)
                FROM table_2
               GROUP BY Order_Id, Order_line_id, Type
            ) AS s
        GROUP BY Type
     ) AS m ;

DROP type IF EXISTS composite_type ;
EXECUTE 'CREATE type composite_type AS (' || column_list || ')';
END ; $$

CREATE PROCEDURE


CALL composite_type() ;

CALL


SELECT t.Order_Id, t.Order_line_id
     , (json_populate_record(null :: composite_type, json_object_agg(t.label, t.Amount))).*
  FROM 
     ( SELECT Order_Id, Order_line_id
            , lower(Type) || (row_number() OVER (PARTITION BY Order_Id, Order_line_id, Type)) :: text AS label
            , Amount
         FROM table_2
     ) AS t
 GROUP BY t.Order_Id, t.Order_line_id ;


order_id
order_line_id
kiwi1
orange1
orange2
apple1
apple2


1
1
45
32
null
0
null

1
2
null
98
76
12
null

1
1001
null
null
null
60
90



SELECT 3

order_id	order_line_id	kiwi1	orange1	orange2	apple1	apple2
1	1	45	32	null	0	null
1	2	null	98	76	12	null
1	1001	null	null	null	60	90