Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (type CHAR primary key, age integer) ;

INSERT INTO table1 VALUES
('A',     '20'),
('B',     '21'),
('C',     '22');

CREATE TABLE table2 (id serial primary key, type CHAR, score integer) ;

INSERT INTO table2 (type, score) VALUES
('A',     10),
('A',     20),
('A',     30),
('B',     40),
('B',     50);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 5


CREATE OR REPLACE PROCEDURE new_type() LANGUAGE plpgsql AS
$$
DECLARE
  column_txt text ;
BEGIN
  SELECT string_agg(' score' || id || ' integer', ',')
   INTO column_txt
    FROM
       (  SELECT count(*) AS count
           FROM table2
          GROUP BY type
          ORDER BY 1 DESC
          LIMIT 1
       ) AS a
   CROSS JOIN LATERAL generate_series(1, a.count :: integer) AS id ;
     
  EXECUTE 'DROP TYPE IF EXISTS new_type' ;
  EXECUTE 'CREATE TYPE new_type AS (' || column_txt || ')' ;
END ;
$$ ;

CALL new_type() ;

CREATE PROCEDURE



CALL


SELECT c.type, c.age
     , (jsonb_populate_record( NULL :: new_type
                             , jsonb_object_agg('score' || c.id, c.score ORDER BY c.score)
                             )).*
  FROM 
     ( SELECT a.type, a.age, b.score, row_number() OVER (PARTITION BY a.type, a.age ORDER BY b.score) AS id
         FROM table1 AS a
         LEFT JOIN table2 AS b
           ON a.type = b.type
     ) AS c
 GROUP BY c.type, c.age
 ORDER BY c.type, c.age


type
age
score1
score2
score3


A
20
10
20
30

B
21
40
50
null

C
22
null
null
null



SELECT 3