crosstab() — Dynamic Pivot Tables with the tablefunc Extension

crosstab() from the tablefunc extension transposes rows into columns at query time. Unlike manual CASE WHEN pivoting, crosstab is cleaner for known column sets and is the only practical option when column names are dynamic.

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

Fixed pivot: monthly tweet counts per hashtag

-- Step 1: source query — must be (row_name, category, value)
SELECT
    h.tag                                       AS row_name,
    TO_CHAR(DATE_TRUNC('month', t.created_at), 'YYYY-MM') AS month,
    COUNT(*)::INT                               AS tweet_count
FROM tw_hashtags h
JOIN tw_tweet_hashtags th ON th.hashtag_id = h.id
JOIN tw_tweets t          ON t.id = th.tweet_id
WHERE t.created_at >= NOW() - INTERVAL '3 months'
GROUP BY h.tag, DATE_TRUNC('month', t.created_at)
ORDER BY h.tag, month;

-- Step 2: wrap in crosstab()
-- NOTE: This is a template — month_1/month_2/month_3 must match the
-- actual months returned by the second-arg category query. Adapt the
-- column declarations to your live data.
/*CTAB_TEMPLATE*/
/*
SELECT * FROM crosstab(
    -- Source query (row_name, category, value) — must be ordered
    $$
    SELECT
        h.tag,
        TO_CHAR(DATE_TRUNC('month', t.created_at), 'YYYY-MM'),
        COUNT(*)::INT
    FROM tw_hashtags h
    JOIN tw_tweet_hashtags th ON th.hashtag_id = h.id
    JOIN tw_tweets t          ON t.id = th.tweet_id
    WHERE t.created_at >= NOW() - INTERVAL '3 months'
    GROUP BY h.tag, DATE_TRUNC('month', t.created_at)
    ORDER BY 1, 2
    $$,
    -- Category list query — defines the output columns
    $$
    SELECT DISTINCT TO_CHAR(DATE_TRUNC('month', created_at), 'YYYY-MM')
    FROM tw_tweets
    WHERE created_at >= NOW() - INTERVAL '3 months'
    ORDER BY 1
    $$
) AS pivot (
    tag TEXT,
    month_1 INT,
    month_2 INT,
    month_3 INT
);
*/