All Articles

Pivot and Unpivot Data in SQL: A Practical Guide

Query Patterns
#pivot#unpivot#conditional-aggregation#sql-interview#sql-patterns

What Pivoting Actually Means

Pivoting turns row values into column headers — reshaping "long" data (one row per fact) into "wide" data (one row per entity, with facts spread across columns). Unpivoting does the reverse: turning wide columns back into rows. This comes up constantly in interviews because it tests something GROUP BY and JOIN questions don't: can you reshape data structurally, not just filter and aggregate it. It's also a daily reality in reporting — dashboards and spreadsheets almost always want wide data, while databases store long data.

The Sample Data

-- sales: one row per product, per quarter (long format)
| product   | quarter | revenue |
|-----------|---------|---------|
| Widget A  | Q1      | 1000    |
| Widget A  | Q2      | 1200    |
| Widget A  | Q3      | 900     |
| Widget A  | Q4      | 1500    |
| Widget B  | Q1      | 800     |
| Widget B  | Q2      | 950     |
| Widget B  | Q3      | 1100    |
| Widget B  | Q4      | 1050    |

The goal of pivoting: turn this into one row per product, with a column for each quarter.

Method 1: Manual Pivot with CASE + Aggregation (works everywhere)

This is the most portable technique — it works identically on PostgreSQL, MySQL, and SQL Server, because it's just CASE expressions wrapped in aggregate functions. It's also almost always what interviewers actually want to see, since it proves you understand the mechanics rather than just knowing a database-specific keyword.

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4
FROM sales
GROUP BY product;

-- Result:
-- | product   | q1   | q2   | q3   | q4   |
-- |-----------|------|------|------|------|
-- | Widget A  | 1000 | 1200 | 900  | 1500 |
-- | Widget B  | 800  | 950  | 1100 | 1050 |

How it works: each CASE expression evaluates to the revenue value only when the quarter matches, and 0 (or NULL) otherwise. Wrapping it in SUM() collapses that down to a single value per product per quarter column — effectively "picking" the one non-zero value out of each group.

Method 2: FILTER Clause (PostgreSQL)

PostgreSQL offers a cleaner syntax for the same idea, using FILTER instead of nesting a CASE inside the aggregate:

SELECT
  product,
  SUM(revenue) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(revenue) FILTER (WHERE quarter = 'Q2') AS q2,
  SUM(revenue) FILTER (WHERE quarter = 'Q3') AS q3,
  SUM(revenue) FILTER (WHERE quarter = 'Q4') AS q4
FROM sales
GROUP BY product;

Functionally identical to Method 1, just more readable — and it correctly returns NULL rather than 0 for a product/quarter combination with no matching rows at all, which is usually the more accurate result.

Method 3: PIVOT Operator (SQL Server / Snowflake / Oracle)

Some databases offer a dedicated PIVOT keyword. It's less portable and, in most engineers' experience, less flexible than the CASE approach once queries get complex — but it's worth recognizing since it appears in SQL Server-specific interview questions.

SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM sales
PIVOT (
  SUM(revenue) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivoted;

Note: standard PostgreSQL and MySQL don't support this PIVOT syntax natively — PostgreSQL has an optional crosstab() function via the tablefunc extension, but the CASE-based approach (Method 1) is the safe, universal default to reach for.

Dynamic Pivoting: When You Don't Know the Columns in Advance

Every method above requires you to know the column values (Q1-Q4) ahead of time. If the pivot columns are dynamic — for example, pivoting by product category where categories change over time — you need to generate the SQL dynamically in application code or a stored procedure, since standard SQL can't produce a variable number of output columns from a single static query.

-- Conceptual pattern (pseudocode) — build the CASE list programmatically, then execute it
categories = SELECT DISTINCT category FROM products
sql = "SELECT product, "
for cat in categories:
    sql += f"SUM(CASE WHEN category = '{cat}' THEN revenue ELSE 0 END) AS {cat}, "
sql += "FROM sales GROUP BY product"
-- execute the generated sql string

This is a common senior-level follow-up question: "what if you don't know the column list ahead of time?" — the correct answer is that static SQL can't do it; you need dynamic SQL generation.

Unpivoting: Turning Columns Back Into Rows

Unpivot is the reverse operation — useful when you receive wide data (e.g. from a spreadsheet import) but need it in normalized, long format for storage or further analysis. The universal, portable technique is UNION ALL:

-- wide_sales: one row per product with a column per quarter
| product   | q1   | q2   | q3   | q4   |
|-----------|------|------|------|------|
| Widget A  | 1000 | 1200 | 900  | 1500 |

SELECT product, 'Q1' AS quarter, q1 AS revenue FROM wide_sales
UNION ALL
SELECT product, 'Q2' AS quarter, q2 AS revenue FROM wide_sales
UNION ALL
SELECT product, 'Q3' AS quarter, q3 AS revenue FROM wide_sales
UNION ALL
SELECT product, 'Q4' AS quarter, q4 AS revenue FROM wide_sales;

-- Result: back to the original long format
-- | product   | quarter | revenue |
-- |-----------|---------|---------|
-- | Widget A  | Q1      | 1000    |
-- | Widget A  | Q2      | 1200    |
-- | Widget A  | Q3      | 900     |
-- | Widget A  | Q4      | 1500    |

PostgreSQL shortcut: the LATERAL join combined with VALUES can achieve the same result in a single pass without repeating the table reference four times:

SELECT product, v.quarter, v.revenue
FROM wide_sales,
LATERAL (VALUES ('Q1', q1), ('Q2', q2), ('Q3', q3), ('Q4', q4)) AS v(quarter, revenue);

SQL Server has a native UNPIVOT operator that mirrors its PIVOT syntax, if you're specifically on that platform.

When to Pivot at the Database Level vs. the Application Level

A frequent interview and design question: should reshaping happen in SQL, or in the application/BI layer? General guidance:

  • Pivot in SQL when the column set is small, fixed, and known ahead of time (fixed quarters, fixed status categories) — it's efficient and keeps the transformation close to the data.
  • Pivot in the application/BI layer (e.g. a pivot table in a dashboard tool, or a pandas pivot() in Python) when the columns are dynamic, user-driven, or change frequently — this avoids fragile dynamic SQL generation.

Common Mistakes

  • Forgetting GROUP BY in the CASE-based pivot — without it, you get one row per original row instead of one collapsed row per entity.
  • Using 0 instead of NULL as the CASE default when a missing combination should genuinely be "no data," not "zero revenue" — this quietly distorts any downstream averages.
  • Hardcoding a pivot's column list in a report that's supposed to update automatically as new categories appear — this is exactly the dynamic-pivoting problem discussed above.
  • Using UNPIVOT/UNION ALL without deduplicating if the source wide table already has multiple rows per entity — you'll multiply rows unexpectedly.

Common Interview Questions on This Pattern

  1. Write a query to pivot monthly sales into one row per year with a column per month. Method 1 (CASE + SUM), generalized to 12 columns.
  2. How would you handle a pivot when you don't know the columns in advance? Explain that static SQL requires a fixed column list, and dynamic SQL (generated in application code or a stored procedure) is required otherwise.
  3. What's the difference between PIVOT and a simple GROUP BY? GROUP BY aggregates into fewer rows; PIVOT (or the CASE-based equivalent) aggregates into fewer rows and spreads values across new columns.
  4. How would you unpivot a table with 12 monthly columns back into a long format? UNION ALL across all 12 columns (Method above), or a database-specific UNPIVOT/LATERAL VALUES construct.

Frequently Asked Questions

Is pivoting in SQL a performance concern on large tables?

The CASE-based approach is just a GROUP BY with conditional aggregation, so its cost scales the same way a normal GROUP BY does — indexing the grouping column helps as usual. Excessive UNION ALL-based unpivoting on very wide tables (dozens of columns) can be slower; consider doing that reshape in the application layer instead.

Do I need a database-specific PIVOT keyword to do this well?

No — the CASE + aggregate pattern (Method 1) works on every major database and is what most engineers use by default, specifically because it's portable and doesn't require learning a vendor-specific extension.

Practice This Pattern

Pivoting is one of the more "spreadsheet-like" SQL skills, and it's a great way to demonstrate reporting-oriented thinking in an interview. Try it hands-on with our SQL practice questions, or reshape a full dataset in our case studies. For more on conditional aggregation, see our SQL interview questions guide.

Back to All Articles