Skip to content

Instantly share code, notes, and snippets.

@romansklenar
Last active February 1, 2023 18:46
Show Gist options
  • Save romansklenar/8086496 to your computer and use it in GitHub Desktop.
Save romansklenar/8086496 to your computer and use it in GitHub Desktop.
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
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
Copy link

rbpdqdat commented Apr 2, 2015

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

@nkgrarima
Copy link

good!!

@alejo17
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
Copy link

great

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment