Pivoting Data in SQL: Rows to Columns (With Examples)

The universal pattern: CASE WHEN + aggregate

Starting from a table with one row per product per month:

SELECT
  product,
  SUM(CASE WHEN sale_month = 'Jan' THEN revenue ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN sale_month = 'Feb' THEN revenue ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN sale_month = 'Mar' THEN revenue ELSE 0 END) AS mar_revenue
FROM sales
GROUP BY product;

Each CASE WHEN only lets a value through when the row matches that column's target category — every non-matching row contributes 0, so the surrounding SUM() effectively isolates each category's total into its own column. This exact pattern works in PostgreSQL, MySQL, and SQL Server with zero syntax differences.

SQL Server's native PIVOT operator

SELECT product, [Jan], [Feb], [Mar]
FROM (
  SELECT product, sale_month, revenue FROM sales
) AS src
PIVOT (
  SUM(revenue) FOR sale_month IN ([Jan], [Feb], [Mar])
) AS pvt;

PIVOT reads naturally once broken down: aggregate revenue, spread across new columns named by the distinct values of sale_month, but only for the values explicitly listed in the IN() clause.

PostgreSQL's crosstab()

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT product, sale_month, revenue FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT sale_month FROM sales ORDER BY 1'
) AS pivoted(product TEXT, jan NUMERIC, feb NUMERIC, mar NUMERIC);

crosstab() requires the extension to be enabled once per database, and the output column list in AS pivoted(...) has to be written out manually — for most day-to-day reporting, the CASE WHEN pattern above is simpler and avoids the extension dependency entirely.

Unpivoting back to rows

-- SQL Server: native UNPIVOT
SELECT product, sale_month, revenue
FROM monthly_revenue_wide
UNPIVOT (revenue FOR sale_month IN (jan_revenue, feb_revenue, mar_revenue)) AS u;

-- PostgreSQL / MySQL: UNION ALL achieves the same result
SELECT product, 'Jan' AS sale_month, jan_revenue AS revenue FROM monthly_revenue_wide
UNION ALL
SELECT product, 'Feb', feb_revenue FROM monthly_revenue_wide
UNION ALL
SELECT product, 'Mar', mar_revenue FROM monthly_revenue_wide;

When the column list isn't known in advance

Every technique above requires naming the target columns explicitly — none of them can pivot on a column list that's only known at query time. Handling a genuinely dynamic set of values (a new product category appearing mid-quarter, for instance) means either generating the SQL string programmatically before running it, or performing the pivot step in the BI/reporting tool downstream, which is usually built to handle dynamic columns more gracefully than raw SQL.

Common mistakes

• Forgetting the ELSE 0 in a CASE WHEN pivot — without it, non-matching rows produce NULL, and SUM() ignores NULLs, which usually still works but can behave unexpectedly with COUNT() or AVG() instead of SUM().
• Assuming PIVOT can accept a subquery-driven column list — the IN() clause needs literal, known values at write time.
• Enabling crosstab() for a one-off report when the CASE WHEN pattern would have done the same job without an extension dependency.
• Hardcoding a pivot for "this quarter's" categories without a plan for what happens when next quarter introduces a new one.