Search code examples
sqlpostgresqlpivot

Using crosstab and pivot

I have a table like so -

MonthID PlanType Count
202004 Medicare 1582
202004 Medicaid 6820
201904 Medicare 3875
201904 Other 12598
201905 Other 41817
201905 Medicare 16886
201905 Medicaid 20877

I want to use pivot to get this output -

Month Medicare Other Medicaid
201905 16886 41817 20877
201904 3875 12598
202004 1582 6820
Solution

  • You can use filtered aggregation:

    select month, 
       sum(count) filter (where plantype = 'Medicare') as medicare,
       sum(count) filter (where plantype = 'Medicaid') as medicaid,
       sum(count) filter (where plantype = 'Other') as other
from the_table
group by month;