PostgreSQL : Aggregate multiple rows from a table into a single record

Question

I need to aggregate multiple rows in a table into a single record using an SQL query. Below is the table structure and the required output details,

Mind you asking this, whom this is required by?

agulowaty
– agulowaty

2022-05-26 09:04:33 +00:00
Commented May 26, 2022 at 9:04 — agulowaty
– agulowaty, Commented May 26, 2022 at 9:04

Tim Biegeleisen · Accepted Answer · 2022-05-26 09:03:31Z

2

This is a basic pivot query requirement. On Postgres, we can try:

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

answered May 26, 2022 at 9:03

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL : Aggregate multiple rows from a table into a single record

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related