How to join and transpose row to columns in redshift

Question

I have following table1 and its unique key is type

type age
A     20
B     21
C     22

And I have following result table. I'd like to join them as

type score
A     10
A     20
A     30
B     40 
B     50

following transposed form.

type  age  score score score
A     20    10    20    30
B     21    40    50    na
C     22    na    na    na

Are there any way to achieve this?

select *
from table1
left join table2 usint(type)

Didn't transpose rows. If someone has opinion please let me know thanks

Answer 1

If you don't know in advance the number of score values per type, then you need a full dynamic solution :

First we create a composite type new_type according to the maximum number of score values per type in table2. This composite type is then used for naming the columns in the final query.

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() ;

Then this query will provide the expected result :

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

The test result is in dbfiddle.