Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL "pivot table" example using tablefunc extension
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)
@fgcarto

This comment has been minimized.

Copy link

fgcarto commented Aug 14, 2014

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 ?

@rbpdqdat

This comment has been minimized.

Copy link

rbpdqdat commented Apr 2, 2015

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

@nkgrarima

This comment has been minimized.

Copy link

nkgrarima commented Oct 16, 2015

good!!

@alejo17

This comment has been minimized.

Copy link

alejo17 commented Dec 15, 2015

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

@rensi4rn

This comment has been minimized.

Copy link

rensi4rn commented Apr 28, 2020

great

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.