PostgreSQL – Pivoting/Crosstab

SELECT mthreport.detail_id, 
    mthreport.jan AS bw_jan, 
    mthreport.feb AS bw_feb, 
    mthreport.mar AS bw_mar, 
    mthreport.apr AS bw_apr, 
    mthreport.may AS bw_may, 
    mthreport.jun AS bw_jun, 
    mthreport.jul AS bw_jul, 
    mthreport.aug AS bw_aug, 
    mthreport.sep AS bw_sep, 
    mthreport.oct AS bw_oct, 
    mthreport.nov AS bw_nov, 
    mthreport."dec" AS bw_dec 
FROM crosstab('SELECT d.detail_id::Integer As row_name, 
                      to_char(dbw.report_month, ''mon'')::text As bucket, 
                      SUM(dbw.amount)::Float As bucketvalue
               FROM usage_data_detail AS d 
                    INNER JOIN usage_data_bandwidth AS dbw 
                    ON d.detail_id = dbw.detail_id
               GROUP BY d.detail_id, to_char(dbw.report_month, ''mon''), date_part(''month'', dbw.report_month)
               ORDER BY d.detail_id'::text, 
     'SELECT to_char(date ''2012-01-01'' + (n || '' month'')::interval, ''mon'') As short_mname FROM generate_series(0,11) n'::text) 
     mthreport(detail_id integer, 
               jan double precision, 
               feb double precision, 
               mar double precision, 
               apr double precision, 
               may double precision, 
               jun double precision, 
               jul double precision, 
               aug double precision, 
               sep double precision, 
               oct double precision, 
               nov double precision, 
               "dec" double precision);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s