sqlpostgresqlpivotpivot-table

Use row values as columns in PostgreSQL


I have the following brands table with total sales per month as a result of a previous query:

id date total
123 Apr-2012 100
123 Mar-2012 150
123 Jan-2012 500
987 Apr-2012 5
987 Mar-2012 0.10
987 Feb-2012 8

I am looking to achieve the following:

id Apr-2012 Mar-2012 Feb-2012 Jan-2012
123 100 150 0 500
987 5 0.10 8 0

How do I use the date values as column labels and fill in missing dates with 0 totals?


Solution

  • A crosstab() query for your example would look like this:

    To fill in 0 for resulting NULL values (request in comment), use COALESCE():

    SELECT brand_id
         , COALESCE(jan, 0) AS "Jan-2012"
         , COALESCE(feb, 0) AS "Feb-2012"
         , COALESCE(mar, 0) AS "Mar-2012"
         , COALESCE(apr, 0) AS "Apr-2012"
    FROM crosstab(
           'SELECT brand_id, month, total
            FROM   brands
            ORDER  BY 1'
    
           ,$$VALUES ('Jan-2012'::text), ('Feb-2012'), ('Mar-2012'), ('Apr-2012')$$
     ) AS ct (
       brand_id int
     , jan numeric    -- use actual data type!
     , feb numeric
     , mar numeric
     , apr numeric);
    

    Detailed explanation and links:

    Aside: I avoided the reserved word in standard SQL "date" as column name (even if Postgres allows it).