How to Create A Pivot Table in PostgreSQL

PostgreSQL enables the user to create tables in the relational database to store data in them using SQL queries. It allows the user to query these tables, fetch data according to the user’s requirements, and gain useful insights through that information. The user can create pivot tables in PostgreSQL to convert data in the rows with the columns data.

This article explains the process of creating a Pivot table in PostgreSQL.

How do you create a pivot table in PostgreSQL?

Pivot tables are created to transform the data from the rows to the columns, and they can be used to get the results according to a particular column in the table. The user can create a pivot table in PostgreSQL using either the crosstab function, which is provided by the tablefunc, or using the CASE statement.

Using Crosstab Function

Use the following query to create a table in the PostgreSQL database.

CREATE TABLE sales (
    product VARCHAR(50),
    region VARCHAR(50),
    year INT,
    revenue DECIMAL(10, 2)
);

The above query creates a table named sales with product, region, year, and revenue columns.

INSERT INTO sales (product, region, year, revenue)
VALUES 
    ('Desktop', 'North', 2020, 10000.00),
    ('Desktop', 'North', 2021, 12000.00),
    ('Desktop', 'South', 2020, 8000.00),
    ('Desktop', 'South', 2021, 9000.00),
    ('Laptop', 'North', 2020, 15000.00),
    ('Laptop', 'North', 2021, 18000.00),
    ('Laptop', 'South', 2020, 10000.00),
    ('Laptop', 'South', 2021, 12000.00);

An extension named tablefunc is required to create a pivot table in PostgreSQL using the Crosstab function. For this purpose, execute the following piece of code.

CREATE EXTENSION IF NOT EXISTS tablefunc;

Running the above command will create the extension.

Once the extension is created, type the following code to create a pivot table using the crosstab function.

SELECT *
FROM crosstab(
    'SELECT product, region, year, revenue
     FROM sales
     ORDER BY 1, 2',
    'SELECT DISTINCT year
     FROM sales
     ORDER BY 1'
) AS pivot_table (
    product VARCHAR,
    region VARCHAR,
    revenue_2020 DECIMAL,
    revenue_2021 DECIMAL
);