SQL window functions: 10 patterns for real analysis
Window functions keep your rows while you calculate across them. Learn ten patterns: ranking, deduplication, running totals, LAG, cohorts and streaks.
← Back to blog
How to Pivot Data in SQL: PIVOT vs CASE WHEN
Short answer: To pivot in SQL (turn row values into columns), wrap each value you want as a column in
SUM(CASE WHEN … THEN … END)andGROUP BYthe rest. This conditional aggregation works in every database. SQL Server, Snowflake, BigQuery, Oracle and DuckDB also have aPIVOToperator that does the same thing with less typing; PostgreSQL and MySQL don’t.
Sooner or later someone asks for “the same data, but with months across the top”. The data in your database is long (one row per region per month), and they want it wide (one row per region, one column per month). This post shows the portable way to do it, the shorter PIVOT syntax in the databases that have it, how to handle columns you don’t know in advance, and how to go back the other way.
What does pivoting actually do?
Pivoting takes the values in one column and turns each distinct value into its own column, aggregating as it goes. Think of a spreadsheet pivot table: rows on the left, a field spread across the top, numbers summed in the middle.
Here’s the long table we’ll use throughout:
CREATE TABLE sales (
region TEXT,
month TEXT,
amount NUMERIC
);
INSERT INTO sales VALUES
('North', '2026-01', 1200), ('North', '2026-02', 1500), ('North', '2026-03', 900),
('South', '2026-01', 800), ('South', '2026-03', 1100),
('West', '2026-02', 600), ('West', '2026-02', 400);And the shape we want:
| region | jan | feb | mar |
|---|---|---|---|
| North | 1200 | 1500 | 900 |
| South | 800 | 1100 | |
| West | 1000 |
Note the two West rows for February. Pivoting always aggregates, so they get summed into one cell.
How do I pivot with CASE WHEN (works everywhere)?
This is called conditional aggregation. Each column is an aggregate that only counts rows matching its condition:
SELECT
region,
SUM(CASE WHEN month = '2026-01' THEN amount END) AS jan,
SUM(CASE WHEN month = '2026-02' THEN amount END) AS feb,
SUM(CASE WHEN month = '2026-03' THEN amount END) AS mar
FROM sales
GROUP BY region
ORDER BY region;How to read it: for each region group, the CASE returns the amount when the row belongs to January and NULL otherwise. SUM ignores NULLs, so the jan column adds up only January rows.
If GROUP BY vs HAVING vs WHERE still feels shaky, it’s worth a quick read first; pivoting is just GROUP BY with a twist.
Should I write ELSE 0 or leave it out?
Leave it out unless you know what you want. Without ELSE, a region with no sales in a month shows NULL. With ELSE 0, it shows 0.
That’s a real difference. NULL says “no rows existed”; 0 says “rows existed and added up to zero”. For a sales report the business usually wants 0, and that’s fine. For something like an average, ELSE 0 quietly drags the result down by counting missing months as zeros. If you want zeros for display, COALESCE(SUM(...), 0) makes the intent explicit.
How do I count instead of sum?
Same pattern, different aggregate:
SELECT
region,
COUNT(CASE WHEN month = '2026-02' THEN 1 END) AS feb_orders
FROM sales
GROUP BY region;COUNT skips NULLs too, so it counts only February rows. MAX, MIN and AVG work the same way.
Is there a cleaner version in PostgreSQL?
Yes. PostgreSQL, SQLite and DuckDB support the FILTER clause, which reads more naturally:
SELECT
region,
SUM(amount) FILTER (WHERE month = '2026-01') AS jan,
SUM(amount) FILTER (WHERE month = '2026-02') AS feb,
SUM(amount) FILTER (WHERE month = '2026-03') AS mar
FROM sales
GROUP BY region;My take: in PostgreSQL, use FILTER. Everywhere else, CASE WHEN. PostgreSQL also has a crosstab() function in the tablefunc extension, but it needs the extension installed and a column type list, and I find conditional aggregation easier to read and review.
How does the PIVOT operator work?
The PIVOT operator does the same job with less repetition. The syntax differs by database.
SQL Server
SELECT region, [2026-01] AS jan, [2026-02] AS feb, [2026-03] AS mar
FROM (
SELECT region, month, amount FROM sales
) AS src
PIVOT (
SUM(amount) FOR month IN ([2026-01], [2026-02], [2026-03])
) AS p;The subquery isn’t decoration. SQL Server’s PIVOT groups by every column in the source that isn’t the pivot column or the aggregated value. If sales had an order_id column and you pivoted the table directly, you’d get one row per order instead of one per region. Selecting only the columns you need first is the safe habit.
Snowflake
SELECT *
FROM (SELECT region, month, amount FROM sales)
PIVOT (SUM(amount) FOR month IN ('2026-01', '2026-02', '2026-03'))
AS p (region, jan, feb, mar);The column alias list at the end renames the output, because otherwise Snowflake names the columns after the quoted values. Snowflake also accepts IN (ANY ORDER BY month) to pivot on every distinct value without listing them.
DuckDB
DuckDB has the friendliest syntax of the lot:
PIVOT sales
ON month
USING SUM(amount)
GROUP BY region;Without an IN list, DuckDB looks up the distinct values of month and creates one column per value. If you’re working on files locally, this alone is a good reason to try it; see DuckDB for analysts.
BigQuery and Oracle also have PIVOT, with syntax close to Snowflake’s. MySQL and PostgreSQL don’t, so CASE WHEN (or FILTER) is the way there.
What if I don’t know the column values in advance?
That’s a dynamic pivot, and standard SQL can’t do it: a query’s output columns must be known before it runs. You have three options.
- Use a database that handles it for you. DuckDB’s
PIVOTwithoutIN, or Snowflake’sIN (ANY). - Generate the SQL. Query the distinct values, build the column list as a string, then run it. In SQL Server:
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(month), ', ') WITHIN GROUP (ORDER BY month)
FROM (SELECT DISTINCT month FROM sales) AS m;
SET @sql = N'SELECT region, ' + @cols + N'
FROM (SELECT region, month, amount FROM sales) AS src
PIVOT (SUM(amount) FOR month IN (' + @cols + N')) AS p;';
EXEC sp_executesql @sql;QUOTENAME wraps each value in brackets, which also protects you if a value contains odd characters.
- Don’t pivot in SQL at all. More on that below.
How do I unpivot (turn columns back into rows)?
Wide tables arrive all the time: an export with one column per month, a survey with one column per question. Unpivoting makes them long again so you can filter, join and aggregate normally.
In DuckDB:
UNPIVOT monthly_sales
ON jan, feb, mar
INTO NAME month VALUE amount;In SQL Server and Snowflake there’s an UNPIVOT operator with a similar shape. In PostgreSQL, a lateral VALUES list does the job:
SELECT m.region, v.month, v.amount
FROM monthly_sales AS m
CROSS JOIN LATERAL (
VALUES ('jan', m.jan), ('feb', m.feb), ('mar', m.mar)
) AS v(month, amount);One trap: SQL Server’s and DuckDB’s UNPIVOT drop rows where the value is NULL by default (DuckDB keeps them with FROM monthly_sales UNPIVOT INCLUDE NULLS (amount FOR month IN (jan, feb, mar))). The VALUES approach keeps them. If you’re counting “months with no sales”, that difference changes your answer.
When should I not pivot in SQL?
More often than you’d think. My rule: keep data long in the database and pivot at the last step, in whatever tool shows it to people.
- Power BI and Tableau want long data. A matrix visual pivots for you, and a long table lets slicers, measures and time intelligence work. Feeding Power BI a wide table with one column per month makes every measure harder. If you’re cleaning data on the way in, Power Query has Pivot Column and Unpivot Columns buttons; Power Query vs DAX covers which steps belong where.
- Excel does it in two clicks. If the output is going into a spreadsheet anyway, a pivot table there is easier for the reader to change.
- Pivot in SQL when the wide shape is the deliverable: a fixed-format export, a feature table for a model, or a report query that someone runs as is.
What would change my mind: a tool that can only display a table exactly as the query returns it. Then the SQL has to do the pivoting, and CASE WHEN is the version the next person will understand.
Frequently asked questions
Does MySQL have a PIVOT function?
No. Use SUM(CASE WHEN … THEN … END) with GROUP BY. For dynamic columns, build the query string with GROUP_CONCAT and run it as a prepared statement.
Is PIVOT faster than CASE WHEN? Usually not in any way you’d notice. Both read the table once and aggregate. Pick the one your team finds easier to read; if a pivot is slow, read the execution plan rather than switching syntax.
Can I pivot more than one value at once?
Yes. With CASE WHEN, add more columns (SUM(...) AS jan_amount, COUNT(...) AS jan_orders). Snowflake, DuckDB and BigQuery also accept several aggregates in one PIVOT.
What’s the difference between pivot and transpose? Transpose swaps rows and columns exactly, with no aggregation. Pivot groups and aggregates as it spreads values into columns. SQL has no transpose; you build it as an unpivot followed by a pivot.
For more patterns you’ll reuse, see the SQL cheat sheet for data analysts and SQL window functions: 10 patterns.
Sources
- Microsoft Learn: FROM - Using PIVOT and UNPIVOT
- Snowflake documentation: PIVOT
- DuckDB documentation: PIVOT statement
- DuckDB documentation: UNPIVOT statement
- PostgreSQL documentation: Aggregate expressions (FILTER)
Last reviewed: 18 October 2026.
Part of: SQL & Local Analytics