在Postgres SQL中将列值更改为列标题替换为另一列中的值

Question

我无法弄清楚如何将列值转换为列标题，并在发生时分配适当的值

假设我有一个Postgres数据库，包含下表：

 Name      Subject       Score      Region
=======   =========     =======     =======
 Joe       Chemistry      20        America 
 Robert    Math           30        Europe
 Jason     Physics        50        Europe
 Joe       Math           70        America
 Robert    Physics        80        Europe
 Jason     Math           40        Europe
 Jason     Chemistry      60        Europe

我希望选择/获取以下形式的数据：

 Name      Chemistry     Math       Physics     Region
=======   ==========    =======    ========    ========
 Joe        20            70         null       America
 Robert     null          30         80         Europe
 Jason      60            40         50         Europe

考虑到有80个主题。如何编写以这种格式返回数据的SQL select语句？

Answer 1

在Postgres中，我推荐使用FILTER语法进行条件聚合：

SELECT name,
       MAX(score) FILTER (WHERE subject = 'Chemistry') AS Chemistry,
       MAX(score) FILTER (WHERE subject = 'Math') AS Math,
       MAX(score) FILTER (WHERE subject = 'Physics') AS Physics
FROM grades
GROUP BY name