Crosstab Patterns (PostgreSQL crosstab())
Generate true pivot tables with the tablefunc extension's crosstab() function.
Crosstab Patterns (PostgreSQL crosstab()) is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why True Crosstab?
CASE pivots require you to list each target column. For genuinely wide pivots (e.g. one column per product), the tablefunc extension's crosstab() is the tool.
Enabling the Extension
tablefunc ships with PostgreSQL contrib:
CREATE EXTENSION IF NOT EXISTS tablefunc;Basic crosstab Signature
crosstab takes a 3-column SQL string (row_key, category, value) and returns row_key + one column per category:
SELECT * FROM crosstab(
$$
SELECT user_id, status, COUNT(*)::INT
FROM orders
GROUP BY user_id, status
ORDER BY user_id, status
$$
) AS ct (
user_id BIGINT,
paid INT,
pending INT,
cancelled INT
);Why You Declare the Output Columns
SQL is statically typed — the planner needs the output columns at parse time. So you specify the schema in the AS clause, including data types.
Two-Argument crosstab (with Category Set)
For sparse data, supply the category list separately so missing values become NULL instead of misaligning:
SELECT * FROM crosstab(
$$
SELECT user_id, status, COUNT(*)::INT
FROM orders GROUP BY user_id, status
ORDER BY user_id
$$,
$$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
user_id BIGINT, paid INT, pending INT, cancelled INT
);When CASE Beats crosstab
For a known, small set of categories, CASE/FILTER is simpler — no extension, no two-arg gotchas. Use crosstab when:
- You have many categories
- Categories are loaded dynamically
- You're generating data for an external pivot consumer
Dynamic Pivots
For runtime-unknown categories, generate the SQL in your app or use PL/pgSQL with format() + EXECUTE.
-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;Pivoting Wide for Spreadsheets
Reports for analysts often want wide format. Generate it in SQL or just hand off long format and let the BI tool pivot.
Unpivot: The Reverse
To go wide → long, use UNION ALL or PostgreSQL's jsonb_each_text():
SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
jsonb_each_text(to_jsonb(monthly_wide) - 'id');Performance
crosstab() runs the inner SQL once and pivots in memory. The bottleneck is the same as a normal GROUP BY query.
Crosstab Limitations
No native PIVOT keyword in PostgreSQL (unlike Oracle/SQL Server). crosstab() is the workaround.
Recap
For most pivots, CASE/FILTER is the clean answer. crosstab() is your tool when categories are many or unknown ahead of time.
Quick Check
Which extension provides PostgreSQL's crosstab() function?
Frequently asked questions
Is the “Crosstab Patterns (PostgreSQL crosstab())” lesson free?
Yes — the full text of “Crosstab Patterns (PostgreSQL crosstab())” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Crosstab Patterns (PostgreSQL crosstab())”?
Generate true pivot tables with the tablefunc extension's crosstab() function. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Crosstab Patterns (PostgreSQL crosstab())” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- UNION, INTERSECT, EXCEPT
- UNION ALL vs UNION (Deduplication Cost)
- CASE Expressions and Pivot Queries
- Crosstab Patterns (PostgreSQL crosstab())