absolutelearners.com

Pivot and Unpivot Patterns with CTEs

Pivot and Unpivot Patterns with CTEs

Pivoting transforms rows into columns (wide format). Unpivoting does the reverse. PostgreSQL doesn't have a native PIVOT statement, but CTEs combined with conditional aggregation make both patterns clean and readable.

Understanding the Transformation

Long format (unpivoted):

| month   | metric    | value |
|---------|-----------|-------|
| 2024-01 | revenue   | 5000  |
| 2024-01 | ec_orders    | 42    |
| 2024-02 | revenue   | 6200  |
| 2024-02 | ec_orders    | 51    |

Wide format (pivoted):

| month   | revenue | ec_orders |
|---------|---------|--------|
| 2024-01 | 5000    | 42     |
| 2024-02 | 6200    | 51     |

Pattern 1: Pivot with CASE + GROUP BY

The standard PostgreSQL pivot technique:

-- Pivot product sales by category into columns
WITH monthly_category_sales AS (
    SELECT
        DATE_TRUNC('month', o.created_at)::DATE AS month,
        p.category,
        SUM(oi.quantity * oi.unit_price)         AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p     ON p.product_id = oi.product_id
    GROUP BY 1, 2
)

SELECT
    month,
    ROUND(SUM(CASE WHEN category = 'Electronics'  THEN revenue END), 2) AS electronics,
    ROUND(SUM(CASE WHEN category = 'Clothing'     THEN revenue END), 2) AS clothing,
    ROUND(SUM(CASE WHEN category = 'Books'        THEN revenue END), 2) AS books,
    ROUND(SUM(CASE WHEN category = 'Sports'       THEN revenue END), 2) AS sports,
    ROUND(SUM(revenue), 2)                                               AS total
FROM monthly_category_sales
GROUP BY month
ORDER BY month;

Key insight: SUM(CASE WHEN ...) returns NULL for months with no matching rows (which you usually want), or you can wrap in COALESCE(..., 0) for zeros.

Pattern 2: Pivot with crosstab() (tablefunc extension)

For dynamic pivot (when you don't know column names in advance), PostgreSQL's crosstab() function is available via the tablefunc extension:

-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Crosstab pivot
SELECT * FROM crosstab(
    -- Query 1: source data (must be ordered by row, category)
    $$
    SELECT
        DATE_TRUNC('month', o.created_at)::TEXT AS month,
        p.category,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p ON p.product_id = oi.product_id
    GROUP BY 1, 2
    ORDER BY 1, 2
    $$,
    -- Query 2: distinct category values
    $$ SELECT DISTINCT category FROM ec_products ORDER BY category $$
) AS pivot_table(
    month TEXT,
    books NUMERIC,
    clothing NUMERIC,
    electronics NUMERIC,
    home NUMERIC,
    sports NUMERIC
);

Trade-off: crosstab() requires knowing the output columns at query time (listed in the AS clause). For truly dynamic pivot, you need to build the SQL string dynamically in application code.

Pattern 3: Unpivot with UNION ALL

Turn wide format into long format:

-- Suppose we have a summary table with multiple metric columns
-- Unpivot it into long format for easier analysis

WITH wide_summary AS (
    SELECT
        DATE_TRUNC('month', o.created_at)::DATE AS month,
        COUNT(DISTINCT o.customer_id)            AS unique_customers,
        COUNT(DISTINCT o.order_id)               AS total_orders,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue,
        ROUND((AVG(oi.quantity * oi.unit_price))::NUMERIC, 2)  AS avg_order_value
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY 1
)

-- Unpivot: each metric becomes its own row
SELECT month, 'unique_customers' AS metric, unique_customers::NUMERIC AS value FROM wide_summary
UNION ALL
SELECT month, 'total_orders',     total_orders::NUMERIC               FROM wide_summary
UNION ALL
SELECT month, 'total_revenue',    total_revenue                       FROM wide_summary
UNION ALL
SELECT month, 'avg_order_value',  avg_order_value                     FROM wide_summary
ORDER BY month, metric;

Example 1: Quarterly Sales by Product Line

WITH quarterly_sales AS (
    SELECT
        p.category,
        'Q' || EXTRACT(QUARTER FROM o.created_at)::TEXT AS quarter,
        EXTRACT(YEAR FROM o.created_at)::TEXT           AS year,
        SUM(oi.quantity * oi.unit_price)                AS revenue
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    JOIN ec_products p     ON p.product_id = oi.product_id
    GROUP BY 1, 2, 3
)

SELECT
    category,
    ROUND(SUM(CASE WHEN year = '2023' AND quarter = 'Q1' THEN revenue END), 2) AS "2023-Q1",
    ROUND(SUM(CASE WHEN year = '2023' AND quarter = 'Q2' THEN revenue END), 2) AS "2023-Q2",
    ROUND(SUM(CASE WHEN year = '2023' AND quarter = 'Q3' THEN revenue END), 2) AS "2023-Q3",
    ROUND(SUM(CASE WHEN year = '2023' AND quarter = 'Q4' THEN revenue END), 2) AS "2023-Q4",
    ROUND(SUM(CASE WHEN year = '2024' AND quarter = 'Q1' THEN revenue END), 2) AS "2024-Q1",
    ROUND(SUM(CASE WHEN year = '2024' AND quarter = 'Q2' THEN revenue END), 2) AS "2024-Q2"
FROM quarterly_sales
GROUP BY category
ORDER BY category;

Example 2: Stock Price Pivot — Month as Columns

Show average monthly closing price per company (months as columns):

WITH monthly_price AS (
    SELECT
        company_id,
        EXTRACT(MONTH FROM price_date)::INT AS month_num,
        AVG(close_price) AS avg_close
    FROM stock_prices
    GROUP BY company_id, month_num
)

SELECT
    c.ticker,
    c.name,
    c.sector,
    ROUND((AVG(CASE WHEN month_num = 1  THEN avg_close END))::NUMERIC, 2) AS jan,
    ROUND((AVG(CASE WHEN month_num = 2  THEN avg_close END))::NUMERIC, 2) AS feb,
    ROUND((AVG(CASE WHEN month_num = 3  THEN avg_close END))::NUMERIC, 2) AS mar,
    ROUND((AVG(CASE WHEN month_num = 4  THEN avg_close END))::NUMERIC, 2) AS apr,
    ROUND((AVG(CASE WHEN month_num = 5  THEN avg_close END))::NUMERIC, 2) AS may,
    ROUND((AVG(CASE WHEN month_num = 6  THEN avg_close END))::NUMERIC, 2) AS jun,
    ROUND((AVG(CASE WHEN month_num = 7  THEN avg_close END))::NUMERIC, 2) AS jul,
    ROUND((AVG(CASE WHEN month_num = 8  THEN avg_close END))::NUMERIC, 2) AS aug,
    ROUND((AVG(CASE WHEN month_num = 9  THEN avg_close END))::NUMERIC, 2) AS sep,
    ROUND((AVG(CASE WHEN month_num = 10 THEN avg_close END))::NUMERIC, 2) AS oct,
    ROUND((AVG(CASE WHEN month_num = 11 THEN avg_close END))::NUMERIC, 2) AS nov,
    ROUND((AVG(CASE WHEN month_num = 12 THEN avg_close END))::NUMERIC, 2) AS dec
FROM monthly_price mp
JOIN companies c ON c.company_id = mp.company_id
GROUP BY c.company_id, c.ticker, c.name, c.sector
ORDER BY c.sector, c.ticker;

Example 3: Sport Stats Matrix

Player stats across multiple seasons, pivoted by season:

WITH season_stats AS (
    SELECT
        player_id,
        season,
        SUM(goals)   AS goals,
        SUM(assists) AS assists
    FROM player_stats ps JOIN matches m ON m.match_id = ps.match_id GROUP BY player_id, season
)

SELECT
    p.name,
    p.position,
    SUM(CASE WHEN season = '2021' THEN goals END) AS goals_2021,
    SUM(CASE WHEN season = '2022' THEN goals END) AS goals_2022,
    SUM(CASE WHEN season = '2023' THEN goals END) AS goals_2023,
    SUM(CASE WHEN season = '2021' THEN assists END) AS assists_2021,
    SUM(CASE WHEN season = '2022' THEN assists END) AS assists_2022,
    SUM(CASE WHEN season = '2023' THEN assists END) AS assists_2023
FROM season_stats ss
JOIN players p ON p.player_id = ss.player_id
GROUP BY p.player_id, p.name, p.position
ORDER BY p.name;

Purchase this course to unlock the full lesson.

Sign up
← Previous Next →