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 
),
pivoting_table as (
    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
)
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
    pivoting_table as sd
group by accmonth
order by accmonth


run \| edit \| history \| help	0