How Do You Pivot Data in SQL?
Learn how to pivot data in SQL using conditional aggregation with CASE, the PIVOT operator and crosstab to turn rows into readable cross-tab columns.
Expected Interview Answer
Pivoting in SQL means rotating rows into columns, turning distinct values of one column into separate columns and aggregating a measure under each, so tall row-per-category data becomes a wide cross-tab.
The portable way is conditional aggregation: GROUP BY the row dimension and wrap a CASE (or FILTER) inside an aggregate like SUM or COUNT for each target column. Some databases add a dedicated PIVOT operator (SQL Server, Oracle), and PostgreSQL offers crosstab via the tablefunc extension. Because SQL requires a fixed column list, pivots with an unknown set of values need dynamic SQL that builds the column list at runtime. The reverse operation, turning columns back into rows, is called unpivoting.
- Produces readable cross-tab reports from normalized data
- Summarizes a measure across categories in one row
- Works portably via CASE-based conditional aggregation
- Reduces post-processing in the application or spreadsheet
- Pairs with UNPIVOT to reshape data both directions
AI Mentor Explanation
Pivoting is like turning a ball-by-ball log into a batting summary grid: instead of one row per delivery, you get one row per batter with columns for runs against each bowler. You group by batter and sum runs under each bowler's column, exactly as conditional aggregation collapses many rows into a wide cross-tab.
Step-by-Step Explanation
Step 1
Pick the axes
Choose the row dimension to GROUP BY and the column dimension whose distinct values become new columns.
Step 2
Choose the measure
Decide the aggregate — SUM, COUNT, AVG — that fills each cell of the pivoted grid.
Step 3
Write conditional aggregation
For each target value write SUM(CASE WHEN col = 'X' THEN measure END) AS X, or use aggregate FILTER (WHERE ...).
Step 4
Group by the row dimension
Add GROUP BY on the row dimension so each output row collapses its many source rows into one.
Step 5
Go dynamic if needed
When the set of column values is unknown or large, generate the column list with dynamic SQL, or use PIVOT/crosstab.
What Interviewer Expects
- Explaining pivot as rows-to-columns rotation
- Writing conditional aggregation with CASE inside SUM/COUNT
- Knowing GROUP BY drives the row dimension
- Awareness of PIVOT operator and PostgreSQL crosstab
- Understanding dynamic SQL is needed for unknown column sets
- Knowing UNPIVOT reverses the operation
Common Mistakes
- Forgetting GROUP BY, so rows are not collapsed
- Putting the aggregate outside the CASE instead of wrapping it
- Assuming PIVOT syntax is portable across all databases
- Not handling NULLs, so empty cells show NULL instead of 0
- Hardcoding columns when the value set is dynamic
Best Answer (HR Friendly)
“Pivoting in SQL means turning rows into columns to make a summary table, like changing a long list of monthly sales into one row per product with a column for each month. The most reliable way is to group the data and use CASE statements inside a SUM so each category becomes its own column.”
Code Example
SELECT
product,
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY product
ORDER BY product;SELECT
product,
SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY product;SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM (SELECT product, quarter, amount FROM sales) src
PIVOT (
SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;Follow-up Questions
- How do you pivot when the set of column values is not known in advance?
- What is the difference between PIVOT and conditional aggregation?
- How does UNPIVOT work and when would you use it?
- How do you handle NULL cells in a pivot result?
- How does PostgreSQL's crosstab function differ from SQL Server PIVOT?
MCQ Practice
1. What does pivoting data accomplish?
Pivoting rotates distinct values from one column into separate columns, aggregating a measure under each.
2. Which is the most portable way to pivot across SQL dialects?
SUM(CASE WHEN ...) conditional aggregation works in virtually every SQL database, unlike the dialect-specific PIVOT operator.
3. Why might a pivot require dynamic SQL?
SQL needs a fixed column list, so an unknown or changing set of pivot values must be built at runtime with dynamic SQL.
Flash Cards
What is pivoting in SQL? — Rotating rows into columns, turning distinct values of a column into separate aggregated columns.
What is the most portable pivot technique? — Conditional aggregation: SUM(CASE WHEN col = 'X' THEN measure END) with GROUP BY.
Which databases have a PIVOT operator? — SQL Server and Oracle; PostgreSQL uses the crosstab function from the tablefunc extension.
Why do dynamic pivots need dynamic SQL? — SQL requires a fixed column list, so unknown value sets must be assembled at runtime.
What reverses a pivot? — UNPIVOT, which turns columns back into rows.