Postgres在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选择语句返回这种格式的数据?

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