How to Use Crosstab in PostgreSQL: Syntax and Examples

In PostgreSQL, use the crosstab function from the tablefunc extension to pivot rows into columns. You write a SQL query that returns three columns: row identifier, category, and value, then pass it to crosstab to transform the data. This helps summarize data in a matrix format.

Syntax

The crosstab function requires a SQL query returning three columns: a row identifier, a category, and a value. The basic syntax is:

• source_sql: Query returning (row_name, category, value)
• category_sql (optional): Query returning categories in order

You call it like this:

SELECT * FROM crosstab('source_sql' [, 'category_sql']) AS ct(row_name_type, category1_type, category2_type, ...);

Each category becomes a column in the output.

sql

SELECT * FROM crosstab(
  'SELECT rowid, category, value FROM your_table ORDER BY 1,2',
  'SELECT DISTINCT category FROM your_table ORDER BY 1'
) AS ct(rowid text, category1 int, category2 int, category3 int);

Example

This example shows how to pivot sales data by product and month using crosstab. It converts rows of sales into columns for each month.

sql

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE sales(
  product TEXT,
  month TEXT,
  amount INT
);

INSERT INTO sales VALUES
('Apple', 'Jan', 100),
('Apple', 'Feb', 120),
('Banana', 'Jan', 90),
('Banana', 'Feb', 110);

SELECT * FROM crosstab(
  'SELECT product, month, amount FROM sales ORDER BY 1,2',
  'SELECT DISTINCT month FROM sales ORDER BY 1'
) AS ct(product TEXT, Jan INT, Feb INT);

Output

 product | Jan | Feb 
---------+-----+-----
 Apple   | 100 | 120
 Banana  |  90 | 110
(2 rows)

Common Pitfalls

Common mistakes when using crosstab include:

• Not enabling the tablefunc extension before use.
• Source query not returning exactly three columns: row identifier, category, and value.
• Category query missing or unordered, causing columns to mismatch.
• Not defining the output column types and names correctly in the AS ct(...) clause.

Always ensure your source query is ordered by row and category for consistent results.

sql

/* Wrong: Missing tablefunc extension */
-- SELECT * FROM crosstab('SELECT ...');

/* Right: Enable extension first */
CREATE EXTENSION IF NOT EXISTS tablefunc;

/* Wrong: Source query returns 2 columns */
-- SELECT * FROM crosstab('SELECT product, amount FROM sales');

/* Right: Source query returns 3 columns */
SELECT * FROM crosstab('SELECT product, month, amount FROM sales ORDER BY 1,2');

Quick Reference

Concept	Description
tablefunc extension	Must be enabled with CREATE EXTENSION before using crosstab
Source query	Returns (row_name, category, value) ordered by row and category
Category query	Optional query listing categories in column order
Output definition	Define output columns and types in AS clause
Ordering	Source query must be ordered by row and category for correct pivot

Key Takeaways

Enable the tablefunc extension before using crosstab in PostgreSQL.

The source query must return exactly three columns: row identifier, category, and value.

Define output columns explicitly in the AS clause to match categories.

Order the source query by row and category for consistent results.

Use crosstab to pivot data from rows into columns for easier analysis.