Pivot Table Creation In PostgreSQL

In this post I will show you how to get data as pivot table in PostgreSQL. That is we will make the row values to appear as columns. Pivot table is a data summarization tool which is used to summarize data and display the result as another table. First we will create a table with columns id, customer, due_date, amount. You can run the below query to create the table.

CREATE TABLE payment
(

id serial primary key not null,

customer character varying,

due_date timestamp without time zone,

amount numeric

);

Next we can insert the values into the table. You can run the below query to insert the values.

insert into payment(customer,due_date,amount) values('customer1','2015-01-01',10000); 

insert into payment(customer,due_date,amount) values('customer2','2015-02-01',15000); 

insert into payment(customer,due_date,amount) values('customer3','2015-03-01',20000); 

insert into payment(customer,due_date,amount) values('customer4','2015-04-01',25000); 

insert into payment(customer,due_date,amount) values('customer5','2015-05-01',30000);

Next you should create the extension ‘tablefunc’. For creating the extension you can write the below query.

CREATE EXTENSION tablefunc;

After that you can run the below query to create the pivot table from the table.

SELECT * FROM crosstab(

$$ select customer,date_part('month', due_date) as month,amount from payment as data order by 1 $$,

$$ SELECT m FROM generate_series(1,12) m $$

) AS (

customer_name character varying, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int);

We have used the crosstab function in PostgreSQL to generate the pivot table from the data. You can see the amount calculated for each customer for each month. This is very helpful in data summarizations.