如何在 redshift 中将行连接并转置为列

我有以下table1其独特的关键是type

type age
A     20
B     21
C     22

我有以下结果表。我想加入他们

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

以下转置形式。

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

有什么办法可以实现这一点吗？

select *
from table1
left join table2 usint(type)

没有转置行。 如果有人有意见请告诉我 谢谢

如果您事先不知道数量score每种类型的值，那么您需要一个完整的动态解决方案：

首先我们创建一个复合类型new_type根据最大数量score每种类型的值table2。然后，该复合类型用于命名最终查询中的列。

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

然后这个查询将提供预期的结果：

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

测试结果在.