PostgreSQL "pivot table" example using tablefunc extension

crosstab.sql

CREATE EXTENSION tablefunc;

CREATE TABLE sales(year int, month int, qty int);
INSERT INTO sales VALUES(2007, 1, 1000);
INSERT INTO sales VALUES(2007, 2, 1500);
INSERT INTO sales VALUES(2007, 7, 500);
INSERT INTO sales VALUES(2007, 11, 1500);
INSERT INTO sales VALUES(2007, 12, 2000);
INSERT INTO sales VALUES(2008, 1, 1000);
INSERT INTO sales VALUES(2009, 5, 2500);
INSERT INTO sales VALUES(2009, 9, 800);

SELECT * FROM sales;
 year | month | qty  
------+-------+------
 2007 |     1 | 1000
 2007 |     2 | 1500
 2007 |     7 |  500
 2007 |    11 | 1500
 2007 |    12 | 2000
 2008 |     1 | 1000
 2009 |     5 | 2500
 2009 |     9 |  800
(8 rows)

SELECT * FROM crosstab(
  $$ SELECT year, month, qty FROM sales ORDER BY 1 $$,
  $$ SELECT m FROM generate_series(1,12) m $$
) AS (
  year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int
);

 year | Jan  | Feb  | Mar | Apr | May  | Jun | Jul | Aug | Sep | Oct | Nov  | Dec  
------+------+------+-----+-----+------+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |      |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |      |     |     |     |     |     |      |     
 2009 |      |      |     |     | 2500 |     |     |     | 800 |     |      |     
(3 rows)

Is there a way to not enter month names individually if I don't care having
year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 ?

Awesome, thanks. This is exactly what I was looking for.

good!!

two question please: what is this CREATE EXTENSION tablefunc; for?
and what if i have words instead of months?

alejo17 - https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905

great