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 columns and be able to fill in missing dates with 0 totals?
A
crosstab()
query for your example would look like this:To fill in
0
for resultingNULL
values (request in comment), useCOALESCE()
:Detailed explanation and links in this related answer:
PostgreSQL Crosstab Query
Aside: not using the reserved word "date" as column name and you shouldn't either, even if Postgres allows it.