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

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