Database Reference
In-Depth Information
Table functions and pivot tables - tablefunc
This is an extension that allows you to return tables with multiple lines. It provides useful
functions for working with pivot tables. It is used to cross-refer values of two variables.
In the following example, you will create a sales table with the columns year, month, and
value. Then you will use this data to make a crosstab by monthly sales.
First, you will install the
tablefunc
extension and insert some sales:
$ heroku pg:psql --app your-app-name
CREATE EXTENSION tablefunc;
CREATE TABLE sales (year integer, month integer, value
float);
INSERT INTO sales (year, month, value) VALUES (2013, 1,
34.56);
INSERT INTO sales (year, month, value) VALUES (2014, 3,
99.50);
INSERT INTO sales (year, month, value) VALUES (2014, 4,
45.99);
INSERT INTO sales (year, month, value) VALUES (2015, 2,
78.99);
Finally, you will use the functions
crosstab
and
generate_series
to create a pivot
table between month and the sales value in each year:
SELECT * FROM crosstab(
'SELECT year, month, value FROM sales',
'SELECT month FROM generate_series(1, 4) month'
)
AS (
year integer,
"Jan" float,
"Feb" float,
"Mar" float,
"Apr" float
);
year | Jan | Feb | Mar | Apr
------+-------+-------+------+-------
