Skip to content

Instantly share code, notes, and snippets.

@tkalfigo
Forked from romansklenar/crosstab.sql
Created March 23, 2016 10:20
Show Gist options
  • Save tkalfigo/bab76c428bdb85217e91 to your computer and use it in GitHub Desktop.
Save tkalfigo/bab76c428bdb85217e91 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment