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)?

Please always provide data as *text*, not as image. Also include the table definition and your version of Postgres. — Erwin Brandstetter, Nov 24 '20 at 19:24

Gordon Linoff · Accepted Answer · 2020-11-24T14:11:35.720

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;

score 1 · Answer 2 · answered Nov 24 '20 at 19:47

To return "extra" columns, you need the 2-parameter form of the crosstab() function (which is typically what you want anyway):

SELECT * 
FROM  crosstab(
   'SELECT student, roll_no, subject, marks
    FROM   dummy
    ORDER  BY 1'
 , $$SELECT unnest('{Geography, History, Language, Maths, Music}'::text[])$$
   ) AS final (
      "Student"   text
    , "Roll No"   text  -- extra column(s) go here
    , "Geography" int
    , "History"   int
    , "Language"  int
    , "Maths"     int
    , "Music"     int
);

db<>fiddle here

Detailed explanation:

Postgres CROSSTAB Query not able to get all the expected Columns

2 Answers2