Postgres CROSSTAB Query not able to get all the expected Columns

Question

I have one table in PostgreSQL which is like this (Img1)

From this table I am trying to achieve this (Img2)

I am trying to do this using CROSSTAB but in doing so I am not able to get Roll No Column. Below is the query that I am using.

SELECT * 
FROM CROSSTAB
('select student, subject, marks from dummy order by 1,2') 
AS FINAL
(
    Student TEXT, 
    Geography NUMERIC,
    History NUMERIC,
    Language NUMERIC,
    Maths NUMERIC,
    Music NUMERIC
);

How to achieve the expected output as I shown in (Img2)?

Answer 1

You could just use conditional aggregation:

select student,
       max(marks) filter (where subject = 'Music') as music,
       max(marks) filter (where subject = 'Maths') as maths,
       max(marks) filter (where subject = 'History') as history,
       max(marks) filter (where subject = 'Language') as language,
       max(marks) filter (where subject = 'Geography') as geography,
       rollno
from t
group by student, rollno;