Pivot Tables in PostgreSQL

To use the pivot table function you need the tablefunc extention for postgres.

CREATE extension tablefunc;

I liked this post : vertabelo post it helped me but I want to add to that one, so please read it first. Then come back.
So consider this SQL

select IDENTIFIER, CATAGORIES, SUM(value) as VALUES 
from TABLE 
group by IDENTIFIER, CATAGORIES

So lets consider our categories being 3 letter Months ie (Jan, Feb,….) and we want to show payments for each month.

select SOME_PRIMARY_KEY, 
       to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
       SUM(PAYMENTS) as TOT_PAID 
from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')

Now to run this have each month as a column we would

 SELECT *   FROM crosstab( '
     select SOME_PRIMARY_KEY, 
            to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
            SUM(PAYMENTS) as TOT_PAID 
      from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
') 
AS final_result(MyPK character varying(20) , Jan numeric,Feb numeric,Mar numeric,Apr numeric,May numeric,Jun numeric,Jul numeric,Aug numeric,Sep numeric, Oct numeric, Nov numeric,Dec numeric) 
)

This does not give us the desired result since it will fill up from the fist column the values and not assign it to the correct column. To get the desired result we will have to add a second parameter to crosstab

 SELECT *   FROM crosstab( '
     select SOME_PRIMARY_KEY, 
            to_char (DATE_PAYMENT_MADE,  ''Mon'')  as MONTH_CODES,
            SUM(PAYMENTS) as TOT_PAID 
      from TABLE 
group by SOME_PRIMARY_KEY, to_char (DATE_PAYMENT_MADE,  ''Mon'')
',
  $$VALUES ('Jan'::text),('Feb'),('Mar'),('Apr'),('May'),('Jun'),('Jul'),('Aug'),('Sep'),('Oct'),('Nov''),('Dec')$$
) 
AS final_result(MyPK character varying(20) , Jan numeric,Feb numeric,Mar numeric,Apr numeric,May numeric,Jun numeric,Jul numeric,Aug numeric,Sep numeric, Oct numeric, Nov numeric,Dec numeric) 
)

Leave a Reply Cancel reply