Advanced Reporting: The Pivot Table

The Pivot Request

Finance wanted a table with months as columns and products as rows, showing sales for each cell. This is a "Pivot."

The Technique: Conditional Aggregation

SELECT

product_name,

SUM(CASE WHEN month = 'Jan' THEN amount END) AS jan_sales,

SUM(CASE WHEN month = 'Feb' THEN amount END) AS feb_sales,

SUM(CASE WHEN month = 'Mar' THEN amount END) AS mar_sales

FROM sales

GROUP BY product_name;

PostgreSQL: CROSSTAB

PostgreSQL has a `tablefunc` extension with `crosstab()` for cleaner pivots.

Pro Tip

Pivots are powerful for reporting but can be hard to maintain if the number of columns is dynamic. Consider generating the SQL programmatically.

*Day 82: Unpivoting Data.*