Pivot in PostgreSQL

Introduction

Pivot (or Pivot Table / Crosstab) is a technique used to transform data from rows to columns. This technique is very popular in data analysis and reporting, helping group scattered data rows into an easy-to-read summary table.

Example of initial data (Long format):

| Year | Quarter | Revenue |
| :--- | :------ | :------ |
| 2023 | Q1      | 100     |
| 2023 | Q2      | 150     |
| 2024 | Q1      | 120     |
| 2024 | Q2      | 180     |

Data after Pivot (Wide format):

| Year | Q1   | Q2   |
| :--- | :--- | :--- |
| 2023 | 100  | 150  |
| 2024 | 120  | 180  |

How to Implement

Unlike SQL Server or Oracle (which have native PIVOT syntax), PostgreSQL does not have a direct PIVOT keyword. You can accomplish this using the following common approaches:

Using conditional aggregate functions (SUM + FILTER or CASE) is the simplest method, requiring no additional extensions and optimizing performance well.
Using the tablefunc extension and the crosstab() function.

Use Cases

Generating sales reports: Summarizing by month, quarter and year.
Matrix statistics: Viewing student scores by subject or click counts by marketing campaign.
Data preparation: Transforming raw data for rendering charts or exporting to Excel or CSV files.

Detail

Create the tables as follows:

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status VARCHAR(20) NOT NULL CHECK (status IN ('PENDING', 'COMPLETED', 'CANCELLED'))
);

CREATE TABLE order_items (
    item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_category VARCHAR(50) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0)
);

After seeding the data, use the following queries to calculate category revenue statistics by month in the year:

-- Query 1
SELECT 
    oi.product_category AS "Category",
    COALESCE(SUM(oi.amount) FILTER (WHERE EXTRACT(MONTH FROM o.order_date) = 1), 0) AS "January",
    COALESCE(SUM(oi.amount) FILTER (WHERE EXTRACT(MONTH FROM o.order_date) = 2), 0) AS "February",
    COALESCE(SUM(oi.amount) FILTER (WHERE EXTRACT(MONTH FROM o.order_date) = 3), 0) AS "March",
    SUM(oi.amount) AS "Total Revenue"
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'COMPLETED'
  AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
GROUP BY oi.product_category
ORDER BY "Total Revenue" DESC;

-- Query 2
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
    $$
        SELECT 
            oi.product_category,
            EXTRACT(MONTH FROM o.order_date) AS month_num,
            SUM(oi.amount) AS total_amount
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        WHERE o.status = 'COMPLETED'
          AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'
        GROUP BY oi.product_category, EXTRACT(MONTH FROM o.order_date)
        ORDER BY 1, 2
    $$,
    $$ VALUES (1), (2), (3) $$
) AS ct (
    product_category VARCHAR(50),
    january NUMERIC(12,2),
    february NUMERIC(12,2),
    march NUMERIC(12,2)
);

Query 1: This approach uses aggregate functions such as COALESCE, SUM and FILTER, where each column represents a month and each row represents a category.

Query 2: This approach uses crosstab to pivot the data.

You need to enable the tablefunc extension to use the crosstab function.
First, retrieve product_category, month and total_amount, where ORDER BY 1, 2 sorts by product_category and month.
How crosstab works requires the query to always return 3 columns, corresponding to product_category, month and total_amount:
- Column 1 (Row ID): The column kept intact as a label, where each unique value forms a row.
- Column 2 (Category): The column whose values are evaluated against $$VALUES (1), (2), (3)$$.
- Column 3 (Value): The actual value filled into the cells.
Therefore, when using $$VALUES (1), (2), (3)$$, it evaluates the value of the month column:
- If it is 1, it places the value of column 3 (total_amount) into the january column of the output.
- If it is 2, it places the value of column 3 (total_amount) into the february column of the output.
- If it is 3, it places the value of column 3 (total_amount) into the march column of the output.
The final part aliases the columns to display.
Since using crosstab is quite complex, in most cases you should prioritize using FILTER first and only consider crosstab if the number of columns to extract is exceptionally large.

Here is how it works when using a MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW mv_monthly_sales_by_category AS
SELECT 
    oi.product_category,
    EXTRACT(YEAR FROM o.order_date) AS order_year,
    EXTRACT(MONTH FROM o.order_date) AS order_month,
    SUM(oi.amount) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'COMPLETED'
GROUP BY 
    oi.product_category, 
    EXTRACT(YEAR FROM o.order_date), 
    EXTRACT(MONTH FROM o.order_date);

CREATE UNIQUE INDEX idx_mv_monthly_sales 
ON mv_monthly_sales_by_category (product_category, order_year, order_month);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales_by_category;

-- Query 1
SELECT 
    product_category AS "Category",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_month = 1), 0) AS "January",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_month = 2), 0) AS "February",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_month = 3), 0) AS "March",
    SUM(total_revenue) AS "Total Revenue"
FROM mv_monthly_sales_by_category
WHERE order_year = 2026
GROUP BY product_category
ORDER BY "Total Revenue" DESC;

-- Query 2
SELECT 
    product_category AS "Category",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_year = 2024), 0) AS "2024",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_year = 2025), 0) AS "2025",
    COALESCE(SUM(total_revenue) FILTER (WHERE order_year = 2026), 0) AS "2026",
    SUM(total_revenue) AS "Total All Time"
FROM mv_monthly_sales_by_category
WHERE order_year IN (2024, 2025, 2026)
GROUP BY product_category
ORDER BY "Total All Time" DESC;

Use a MATERIALIZED VIEW to precalculate and store revenue for each category by month.
Supports various report types, such as monthly or quarterly reports.
You need to create a UNIQUE INDEX to enable REFRESH MATERIALIZED VIEW CONCURRENTLY, along with a scheduled cron job to update data periodically.