Pivot the Hard Way., PostgreSQL

Pivot the Hard Way.

with your_table (account, accmonth, amount) as (
	select 'BA', 201701, 7   union all
    select 'BA', 201701, 8   union all
    select 'BA', 201701, 9   union all
	select 'BA', 201702, 20  union all
    select 'BA', 201703, 30  union all
    select 'BU', 201701, 320 union all
	select 'BU', 201702, 420 union all
    select 'CA', 201702, 33  union all
	select 'CA', 201703, 21  union all
	select 'MA', 201701, 228 union all
	select 'MI', 201702, 219 union all
	select 'SC', 201702, 19  union all
	select 'VA', 201703, 165 
)

select accmonth,sum(ba) as ba,sum(bu) as bu,sum(ca) as ca,sum(ma) as ma,sum(mi) as mi,sum(sc) as sc,sum(va) as va

from (
select accmonth,
    case when account = 'BA' then sum(amount) end as BA,
	case when account = 'BU' then sum(amount) end as BU,
	case when account = 'CA' then sum(amount) end as CA,
	case when account = 'MA' then sum(amount) end as MA,
	case when account = 'MI' then sum(amount) end as MI,
	case when account = 'SC' then sum(amount) end as SC,
	case when account = 'VA' then sum(amount) end as VA
from your_table
    
group by accmonth, account

) as sd

group by accmonth

order by accmonth


run \| edit \| history \| help	0