CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstabunpivot
Naved Ansari
Naved Ansari

Reputation: 660

Postgres CROSSTAB Query not able to get all the expected Columns

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

enter image description here

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

enter image description here

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

Upvotes: 0

Views: 2489

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656566

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:

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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;

Upvotes: 2

Related Questions