How to Pivot Data in SQL: Rows to Columns Without a Pivot Function

The Problem

You have long-format data — one row per (product, month, amount) — and you need wide format: one row per product, with each month as a column. This is a pivot, and the function that does it (SQL Server's PIVOT, PostgreSQL's crosstab) varies by database. The portable trick that works everywhere: conditional aggregation.

The Conditional Aggregation Pattern

SELECT product, MAX(CASE WHEN month = '2026-01' THEN amount END) AS jan, MAX(CASE WHEN month = '2026-02' THEN amount END) AS feb FROM sales GROUP BY product; — each CASE creates one column; the outer aggregate (MAX or SUM) collapses the rows. Works identically in MySQL, PostgreSQL, SQLite, and SQL Server. Use SUM for quantities, MAX for single values, and wrap with COALESCE to fill missing cells.

When the Values Aren't Aggregatable

If the cell value is a string or an ID, MAX/CASE still works — MAX picks the only non-null value per group. If the source has multiple values per cell (two orders in the same month for the same product), aggregate first: SELECT product, month, SUM(amount) AS total FROM sales GROUP BY 1,2, then pivot the aggregated result.

The Database-Native Alternatives

SQL Server: SELECT * FROM (SELECT product, month, amount FROM sales) src PIVOT (SUM(amount) FOR month IN ([2026-01],[2026-02])) p; — cleaner but requires listing columns. PostgreSQL: enable the tablefunc extension and use crosstab, which also needs the column list. MySQL: no native pivot — conditional aggregation is the way. For any database, conditional aggregation is more portable and just as fast on small-to-medium datasets.

The Dynamic Pivot (No Hardcoded Columns)

When months grow (or you don't know them), build the column list dynamically: generate the CASE expressions in your app layer, or use a prepared statement with STRING_AGG (PostgreSQL) / GROUP_CONCAT (MySQL) to construct the query text, then EXECUTE it. The pattern: SELECT STRING_AGG(DISTINCT format('MAX(CASE WHEN month = ''%s'' THEN amount END) AS "%s"', month, month), ', ') FROM sales — assemble, execute, done.

When to Un-Pivot Instead

If you're pivoting for reporting, remember most BI tools and charting libraries prefer long format. Pivot in SQL only when the consumer (spreadsheet, API response, legacy app) needs wide format. Pivoting back to long when you need to re-aggregate is a classic anti-pattern — keep the data long and pivot at the presentation layer.