Introduction

In this article, I will explain how to create pivot tables in PostgreSQL using the \crosstabview meta-command introduced in version 9.6.

Setup

I will be using the dvdrentals sample database. In this part, I'll show how to setup the database. If you already have it setup or do not wish to install it, you can skip this part.

First, I create the database:

$ su - postgres
$ psql
psql (11.7 (Debian 11.7-0+deb10u1))
Type "help" for help.

postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE
postgres=# \q

Then I download the dump file from postgresqltutorial.com and I restore the database using the pg_restore command.

$ wget https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
$ unzip dvdrental.zip
$ pg_restore -U postgres -d dvdrental dvdrental.tar
$ psql -U postgres -d dvdrental
psql (11.7 (Debian 11.7-0+deb10u1))
Type "help" for help.

dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

Pivot tables

In this example, I want to get the total amount of sales per month for each employee.

I want the result to look like this:

|          | Employee 1 | ... | Employee n |
|----------|------------|-----|------------|
| January  |            |     |            | 
| February |            |     |            | 
| ...      |            |     |            |
| December |            |     |            |

Each cell of the table will contain the total amount of sales for the employee on that mounth.

Explore the database

Here is an extract of the database ERD that shows the two tables we are interested in:
ERD samples

The sales are recorded in the payment table.

For each payment, we have a staff_id and a payment_date.

I can join the two tables on the staff_id column.

Extract sales per staff

First, we need to create a query that will extract the three attributes I want in my table: the month, the employee's name and the total amount.

Month:
We have a datetime value stored in payment.payment_date. I'd like to have a month in letters to make it easier to read. I'll use the to_char() to convert the timestamp into a string.

to_char(payment.payment_date, 'Month') AS month

Employee:
I will concatenate the first and last names to get a full name. This will be our columns headers.

staff.first_name || ' ' || staff.last_name AS staff_name

Total amount:
The total amount is simply the sum of all the amounts from the payment table grouped by staff and month.

sum(payment.amount)

My query is as follow:

SELECT 
    to_char(payment.payment_date, 'Month') AS month
    , staff.first_name || ' ' || staff.last_name AS staff_name
    , sum(payment.amount) AS total
FROM 
    payment
        LEFT JOIN staff
            ON payment.staff_id = staff.staff_id
GROUP BY
    staff_name
    , month 
    ,  date_part('month', payment.payment_date)
ORDER BY 
    staff_name
    , date_part('month', payment.payment_date);

And the output looks like this

   month   |  staff_name  |  total
-----------+--------------+----------
 February  | Jon Stephens |  4191.00
 March     | Jon Stephens | 12109.73
 April     | Jon Stephens | 14479.10
 May       | Jon Stephens |   280.09
 February  | Mike Hillyer |  4160.84
 March     | Mike Hillyer | 11776.83
 April     | Mike Hillyer | 14080.36
 May       | Mike Hillyer |   234.09
(8 rows)

Generate the pivot table

Now, to generate the pivot table, I just have to append the crosstabview command to my query.
The order of the selected columns in the query is important as it will determine which is used for rows, columns or values (cells).
The order should be as follow:

SELECT
    rows
    , columns
    , values

So the final query is like this:

SELECT 
    to_char(payment.payment_date, 'Month') AS month
    , staff.first_name || ' ' || staff.last_name AS staff_name
    , sum(payment.amount) AS total
FROM 
    payment
        LEFT JOIN staff
            ON payment.staff_id = staff.staff_id
GROUP BY
    staff_name
    , month 
    ,  date_part('month', payment.payment_date)
ORDER BY 
    staff_name
    , date_part('month', payment.payment_date)
\crosstabview

It outputs the pivot table I wanted:

   month   | Jon Stephens | Mike Hillyer
-----------+--------------+--------------
 February  |      4191.00 |      4160.84
 March     |     12109.73 |     11776.83
 April     |     14479.10 |     14080.36
 May       |       280.09 |       234.09
(4 rows)