PostgreSQL :将表中的多个行聚合到单个记录中

Question

我需要使用SQL查询将表中的多个行聚合到单个记录中。下面是表结构和所需的输出详细信息，

​

​

​

​

Answer 1

这是一个基本的枢轴查询需求。在Postgres，我们可以尝试：

SELECT id, name,
       'english' AS subject1,
       MAX(marks) FILTER (WHERE subject = 'english') AS marks1,
       'math' AS subject2,
       MAX(marks) FILTER (WHERE subject = 'math') AS marks2,
       'science' AS subject3,
       MAX(marks) FILTER (WHERE subject = 'science') AS marks3
FROM yourTable
GROUP BY id, name;

​

​

​

Demo

​