SQL Pivot and Unpivot Guide

SQL PIVOT and UNPIVOT are data reshaping techniques used when the structure of your result matters just as much as the values inside it.

That matters because many SQL tables are stored in a normalized, row-based format that is great for:

• writes
• joins
• filtering
• transactions
• and general relational design

But the people consuming the data often want something different.

They may want:

• one row per product with separate month columns
• one row per employee with one column for each score type
• one row per region with separate totals for each quarter
• or the reverse, where a wide spreadsheet-like table needs to be turned back into a row-based structure for analysis

That is where pivoting and unpivoting become useful.

This guide explains SQL pivot and unpivot clearly, including:

• what each one does
• when you should use them
• why conditional aggregation is often just as important
• how static and dynamic pivots differ
• what common mistakes to avoid
• and how to think about reshaping data in real reporting and data engineering workflows

Why pivoting matters

A lot of SQL data starts in a shape that is good for storage but not ideal for reporting.

For example, suppose sales data looks like this:

product_name	month	revenue
Laptop	Jan	1000
Laptop	Feb	1200
Laptop	Mar	900
Mouse	Jan	300
Mouse	Feb	450
Mouse	Mar	500

This structure is normal and useful. It is tidy, row-based, and easy to aggregate.

But a business report may want this instead:

product_name	Jan	Feb	Mar
Laptop	1000	1200	900
Mouse	300	450	500

That second layout is often easier for:

• dashboards
• exported reports
• spreadsheet-style analysis
• presentation tables
• side-by-side comparisons

That is what pivoting does.

The most important rule

Before anything else, remember this:

PIVOT changes the shape of the result, not the meaning of the underlying data.

That is the most important idea in this topic.

A pivot does not create new facts. It reorganizes existing facts so they are easier to read in a specific layout.

That means pivoting is mainly about:

• presentation
• reporting shape
• comparative readability
• and downstream convenience

It is not usually the best default storage format for operational relational data.

This is why many strong systems:

• store data in row-based relational form
• then pivot it only when the output needs to look different

That is a very practical mindset.

What PIVOT means in SQL

A pivot turns values from rows into columns.

In simple terms:

• row categories become column headers
• aggregated values fill those columns

The most common pivot pattern involves:

• one grouping column
• one category column that becomes multiple output columns
• one numeric or measurable value column
• and an aggregate such as SUM, COUNT, or AVG

That is why pivoting is often closely tied to reporting queries.

What UNPIVOT means in SQL

Unpivot is the reverse idea.

It turns columns into rows.

This is useful when data is already stored in a wide format like:

employee_name	q1_score	q2_score	q3_score	q4_score
Alice	90	88	91	95
Bob	80	82	79	85

But you want a row-based structure like:

employee_name	quarter	score
Alice	q1_score	90
Alice	q2_score	88
Alice	q3_score	91
Alice	q4_score	95
Bob	q1_score	80
Bob	q2_score	82
Bob	q3_score	79
Bob	q4_score	85

That makes the data easier to:

• filter
• group
• chart
• aggregate
• and normalize for downstream analytics

That is what unpivoting is for.

Why pivoting is common in reporting

Pivoting is especially useful in reporting because many reports want side-by-side comparison.

Examples:

• monthly revenue per product
• quarterly totals per region
• ticket counts by status
• survey results by response category
• count of users by role
• sales by year

A row-based table is still relationally clean, but the pivoted result is often easier for a human to scan quickly.

That is why pivoting is often the last step in a report query rather than the first step in a database model.

Why unpivoting is common in data engineering

Unpivoting is especially useful when the source data arrives in a wide or spreadsheet-like format.

Examples:

• CSV exports with one column per month
• survey imports with one column per answer slot
• manually maintained spreadsheets
• legacy tables with repeating measure columns
• wide operational snapshots

These formats are often not ideal for analytics because row-based models are easier to:

• filter
• group
• join
• and aggregate

That is why data engineers often unpivot source data before building more reliable downstream models.

A simple pivot example

Let’s go back to this sales table:

product_name	month	revenue
Laptop	Jan	1000
Laptop	Feb	1200
Laptop	Mar	900
Mouse	Jan	300
Mouse	Feb	450
Mouse	Mar	500

A pivot result would turn month values into columns:

product_name	Jan	Feb	Mar
Laptop	1000	1200	900
Mouse	300	450	500

This is one of the most common pivot shapes.

The important thing to notice is:

• product_name stays as the row grouping field
• month values become columns
• revenue values are placed into those new columns

That is the essence of pivoting.

PIVOT is usually an aggregate operation

A key detail is that pivoting usually needs aggregation.

Why?

Because once you turn row categories into columns, you often need to decide:

• what value goes into that cell?

If more than one row could fit the same group-and-column intersection, SQL needs a rule such as:

• SUM
• COUNT
• AVG
• MAX
• MIN

That is why pivoting is often really:

• grouped aggregation plus reshaping

This is one reason pivoting is so closely related to GROUP BY.

Conditional aggregation: the most practical pivot technique

Even though some databases provide a dedicated PIVOT keyword, one of the most practical and portable ways to pivot data is conditional aggregation.

This is often the best method to learn first because:

• it works in many SQL environments
• it is explicit
• it is flexible
• and it teaches the logic behind pivoting clearly

Example:

SELECT
    product_name,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS Mar
FROM sales
GROUP BY product_name;

This produces the pivoted result.

That is one of the most useful SQL pivot patterns you can learn.

Why conditional aggregation is so powerful

Conditional aggregation works because each expression says:

• only include revenue in this column when the row matches the desired category

So:

SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END)

means:

• add revenue only for January rows
• otherwise add zero

When grouped by product_name, this creates one January total per product.

Do the same for February and March, and you have a pivot.

This technique is extremely useful because it is:

• readable
• flexible
• and widely supported

When dedicated PIVOT syntax is useful

Some SQL systems provide dedicated PIVOT syntax.

That can make pivot queries shorter or more formal in some environments.

But there are two important points:

1. Syntax varies by database

Pivot syntax is not equally portable across systems.

2. Conditional aggregation is often still easier to control

Especially when the pivot needs custom logic or more portability.

That is why many teams still prefer conditional aggregation even when a native pivot feature exists.

The important skill is understanding the pivot logic itself, not memorizing one vendor’s syntax only.

Static pivot versus dynamic pivot

This is one of the most important design distinctions.

Static pivot

You already know the output columns in advance.

Example:

• Jan
• Feb
• Mar

Dynamic pivot

The output columns are not known ahead of time and must be generated from the data.

Example:

• category names can change
• months present in the table may vary
• statuses are not fixed
• survey answers vary by dataset

Static pivots are simpler and safer. Dynamic pivots are more flexible but more complex.

This distinction matters a lot in real systems.

Static pivot is easier and safer

If you already know the categories, static pivoting is usually the best choice.

Example:

• four known quarters
• three known score types
• known status buckets
• a small controlled list of categories

This is easier because:

• the query is simpler
• the output shape is predictable
• downstream tools know what columns to expect
• and validation is easier

That is why many production reporting queries prefer static pivots where possible.

Dynamic pivot is more flexible but more complex

Dynamic pivoting is needed when the categories are not fixed.

For example:

• every month currently present in the data
• a variable set of product categories
• custom survey questions
• user-defined tags

This usually requires:

• dynamic SQL generation
• controlled building of column lists
• and extra care in the application or reporting layer

Dynamic pivots can be useful, but they are often harder to:

• secure
• test
• cache
• and integrate with fixed-schema consumers

So they should be used deliberately.

A practical rule for dynamic pivoting

A useful rule is:

If the consuming UI or report expects a fixed known shape, prefer a static pivot. If the categories are truly variable and the consumer can handle changing columns, dynamic pivoting may make sense.

That is the practical decision boundary.

A simple unpivot example

Suppose you have this wide table:

product_name	jan_revenue	feb_revenue	mar_revenue
Laptop	1000	1200	900
Mouse	300	450	500

You want this row-based output:

product_name	month	revenue
Laptop	Jan	1000
Laptop	Feb	1200
Laptop	Mar	900
Mouse	Jan	300
Mouse	Feb	450
Mouse	Mar	500

That is an unpivot.

It turns repeated measure columns into rows.

Why unpivoting is useful

Unpivoting is useful because wide formats are often awkward for analysis.

For example, once revenue is split into separate columns like:

• jan_revenue
• feb_revenue
• mar_revenue

it becomes harder to:

• group by month
• chart trends over time
• calculate rolling summaries
• compare arbitrary periods
• or join to a time dimension cleanly

The row-based format is usually more flexible for SQL analysis.

That is why unpivoting is often a preparatory step in analytics and data engineering.

Unpivot can often be done with UNION ALL

Just like pivoting can often be done without dedicated PIVOT syntax, unpivoting can often be done with UNION ALL.

Example:

SELECT product_name, 'Jan' AS month, jan_revenue AS revenue
FROM monthly_sales
UNION ALL
SELECT product_name, 'Feb' AS month, feb_revenue AS revenue
FROM monthly_sales
UNION ALL
SELECT product_name, 'Mar' AS month, mar_revenue AS revenue
FROM monthly_sales;

This produces the unpivoted row-based structure.

This is a very useful and portable technique.

Why UNION ALL is often better than UNION here

When unpivoting, you usually want to preserve every row transformation exactly.

That means UNION ALL is normally better than UNION because:

• UNION ALL keeps all rows
• UNION removes duplicates

If two unpivoted rows happen to look identical, UNION could remove them, which would usually be wrong.

So in unpivot patterns, UNION ALL is usually the safer choice.

Pivoting works best on tidy row-based input

A very important practical point is this:

Pivoting is easiest when the input data is already tidy and row-based.

That usually means each row represents:

• one entity
• one category
• one measure

For example:

• one product
• one month
• one revenue value

That shape is ideal for pivoting.

If the input data is messy, duplicated, or already partially wide, pivot queries become harder and more error-prone.

This is why good data preparation matters so much before pivoting.

Choose the right grouping key before pivoting

A very common pivot mistake is using the wrong row grain.

For example, if you pivot sales by month but forget that the source rows are really:

• one row per product per region per month

then grouping only by product may collapse regions together unintentionally.

That is why the right question before pivoting is:

• what should one row in the final result represent?

Examples:

• one row per product
• one row per employee
• one row per region
• one row per customer

That row definition controls the grouping logic of the pivot.

Aggregation mistakes are one of the biggest pivot problems

Because pivoting usually involves aggregation, a wrong aggregate can silently create wrong results.

Examples:

• using COUNT when the business wants SUM
• using MAX to hide duplicates instead of fixing the underlying row shape
• summing rows that were already duplicated by a join
• grouping at the wrong level before pivoting

That is why pivot queries require the same discipline as any grouped reporting query:

• understand the source grain
• validate row counts
• and make sure the aggregate reflects the business meaning

NULL handling in pivoted results

Pivoted outputs often produce NULL values when a category does not exist for a group.

Example: if Mouse has no March sales, then the pivot may show:

product_name	Jan	Feb	Mar
Mouse	300	450	NULL

That is not necessarily wrong. It means:

• there was no source value for that group-category combination

Sometimes NULL is the right output. Sometimes you may want to replace it with 0.

This is where COALESCE can help if the business meaning is correct.

Example:

COALESCE(SUM(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar

That gives 0 instead of NULL.

When to use 0 versus NULL in pivoted reports

This is an important reporting decision.

Use 0 when the business meaning is:

• there were zero units or zero revenue for that category

Use NULL when the business meaning is closer to:

• no data exists
• not applicable
• or not measured

That distinction matters.

A pivot cell with no source data is not always the same as zero. So do not replace NULL casually unless the business meaning is clear.

Pivoting for reporting versus storage

Pivoted tables are often great for:

• display
• export
• summary tables
• executive reports

But they are usually not ideal for long-term storage as a primary operational model.

Why?

Because wide pivoted structures are often harder to:

• extend
• join
• aggregate flexibly
• filter by arbitrary categories
• and maintain when categories change

That is why pivoting is most often a query-layer or serving-layer concern, not the default storage model for transactional truth.

Unpivoting often moves data toward better analytics structure

This is the opposite pattern.

If source data arrives in a wide shape like:

• q1_sales
• q2_sales
• q3_sales
• q4_sales

then unpivoting it into:

• quarter
• sales

often makes the dataset much easier to work with analytically.

That is why unpivoting is so common in:

• ETL
• data warehouses
• spreadsheet imports
• survey processing
• and legacy system cleanup

It often converts awkward wide source layouts into much more SQL-friendly row-based models.

Pivot and unpivot are often part of larger workflows

In real systems, pivot and unpivot are usually not isolated tricks. They are steps inside larger workflows.

Examples:

Data analysis workflow

Raw rows → grouped summary → pivoted report

Data engineering workflow

Wide import → unpivot → cleaned row-based model → downstream marts

Backend export workflow

Transactional tables → aggregated result → pivoted CSV export

This is why it helps to think of pivoting and unpivoting as:

• data reshaping tools not
• isolated SQL party tricks

That mindset makes them much more useful.

Common mistakes with pivoting

There are a few recurring pivot mistakes.

1. Forgetting that pivoting usually requires aggregation

If multiple rows fit the same pivot cell and no correct aggregate is chosen, the result will be wrong or undefined.

2. Pivoting data at the wrong grain

This often causes accidental over-aggregation.

3. Using pivoted output as though it were a good long-term normalized storage model

It usually is not.

4. Ignoring NULL meaning in pivot cells

Missing data is not always the same as zero.

5. Building dynamic pivots without controlling the category list safely

Dynamic SQL needs extra care.

Common mistakes with unpivoting

There are also classic unpivot mistakes.

1. Using UNION instead of UNION ALL

This can accidentally remove valid rows.

2. Unpivoting columns that do not represent the same logical measure family

For example, combining unrelated columns into one measure field can create nonsense.

3. Losing the meaning of the original column names

When you unpivot, the new category column must clearly preserve what each original column represented.

4. Forgetting that wide input may contain mixed data types or mixed business meanings

Those should usually be standardized before or during unpivoting.

Practical examples

Example 1: pivot monthly revenue with conditional aggregation

SELECT
    product_name,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS Mar
FROM sales
GROUP BY product_name;

Example 2: pivot status counts per team

SELECT
    team_name,
    SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END) AS open_count,
    SUM(CASE WHEN status = 'Closed' THEN 1 ELSE 0 END) AS closed_count,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending_count
FROM tickets
GROUP BY team_name;

Example 3: unpivot quarterly scores into rows

SELECT employee_name, 'Q1' AS quarter, q1_score AS score
FROM employee_scores
UNION ALL
SELECT employee_name, 'Q2' AS quarter, q2_score AS score
FROM employee_scores
UNION ALL
SELECT employee_name, 'Q3' AS quarter, q3_score AS score
FROM employee_scores
UNION ALL
SELECT employee_name, 'Q4' AS quarter, q4_score AS score
FROM employee_scores;

Example 4: replace NULL pivot cells with zero

SELECT
    product_name,
    COALESCE(SUM(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan,
    COALESCE(SUM(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb,
    COALESCE(SUM(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar
FROM sales
GROUP BY product_name;

These are all practical patterns you can use in real SQL work.

FAQ

What is PIVOT in SQL?

PIVOT is a technique that turns row values into columns, usually for reporting or summary output such as monthly totals shown across separate month columns.

What is UNPIVOT in SQL?

UNPIVOT is the reverse operation. It turns multiple columns into rows so that wide table data becomes a more normalized or analysis-friendly row-based structure.

Do all SQL databases support PIVOT and UNPIVOT the same way?

No. The idea is widely understood, but the exact syntax varies by database. In many cases, conditional aggregation is the most portable alternative to vendor-specific PIVOT syntax.

When should I use conditional aggregation instead of PIVOT?

Use conditional aggregation when you want more portability, more control, or when your database does not support PIVOT directly. It is one of the most practical ways to build pivot-style results in SQL.

Final thoughts

SQL pivot and unpivot techniques are really about reshaping data so it fits the task at hand.

The key ideas to remember are:

• PIVOT turns rows into columns
• UNPIVOT turns columns into rows
• pivoting is usually an aggregation problem plus a presentation problem
• conditional aggregation is one of the most practical pivot techniques
• static pivots are simpler than dynamic pivots
• unpivoting is often great for analytics and data engineering cleanup
• and the best storage model is usually still relational and row-based, with pivoting applied when the output needs a different shape

If you understand those ideas clearly, pivot and unpivot stop feeling like niche SQL tricks and start becoming very practical tools for reporting, transformation, and analytics work.