如何将行连接并转到红移中的列

Question

我遵循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)

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

Answer 1

如果您不预先知道每种类型的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

测试结果在[医]小提琴中。