CODEWITHSUNDEEP
sqlpostgresql
Abhishek
Abhishek

Reputation: 3068

Pivoting equivalent in postgresql

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!

Upvotes: 0

Views: 2156

Answers (1)

Taryn
Taryn

Reputation: 247700

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

Upvotes: 1

Related Questions