Pivoting equivalent in postgresql

Question

I have the following statement in SQL Server where I am using pivot:

Select YR, [1] As Beginning, [2] As Inter, [3] As Advanced, [4] As AdvHigh,Campus
From    
    (Select YR, Rating, StudentId, Campus 
        from mytable
        Where YR = '2014'
    ) As Data
    PIVOT   (Count(StudentId)
            For Rating IN ([1], [2], [3], [4])
    ) As Pvt

Now I am trying to write the same query in Postgresql (I am a newbie to postgresql). I have looked at tablefunc but am not exactly sure how to use it yet.

Any help will be great!

Can you provide an SQLFiddle (http://sqlfiddle.com/) with example input and the example output? — Wolph, Sep 29 '14 at 15:30

score 1 · Accepted Answer · answered Sep 29 '14 at 15:36

1

I'm not overly familiar with tablefunc in postgresql but you can replicate a PIVOT by using a CASE expression with an aggregate function:

Select YR, 
  sum(case when ListeningProfRating = 1 then 1 else 0 end) As Begining,
  sum(case when ListeningProfRating = 2 then 1 else 0 end) As Inter,
  sum(case when ListeningProfRating = 3 then 1 else 0 end) As Advanced,
  sum(case when ListeningProfRating = 4 then 1 else 0 end) As AdvHigh
  Campus 
from mytable
Where YR = '2014' 
  AND ListeningScoreCode IN('S', 'B')
group by yr, campus

answered Sep 29 '14 at 15:36

Taryn

242,637
56
362
405

hi...thank you for the solution. it worked great. is there a way we could use crosstab here for the pivot? – Abhishek Sep 29 '14 at 16:42
@crozzfire Yes, but I'm not familiar with that syntax - see this question/answer -- http://stackoverflow.com/questions/3002499/postgresql-crosstab-query – Taryn Sep 29 '14 at 17:22

Pivoting equivalent in postgresql

1 Answers1