How to UNPIVOT Data in SQL with UNION ALL

Most SQL tables are easiest to analyze when each row represents one observation at one grain.

But real data does not always arrive that way.

Exports from spreadsheets, finance systems, and internal tools often show up in a wide format:

• one row per product
• one column per month
• one column per metric

That layout is easy for a person to scan. It is much harder to filter, group, join, and chart in SQL.

The fix is to UNPIVOT the data: turn columns back into rows.

Some databases have a dedicated UNPIVOT operator, but the most portable pattern is still plain old UNION ALL. In this guide, we will use that approach so the logic stays readable and works across more than one engine.

A Real Business Scenario: Spreadsheet-Friendly Input, SQL-Unfriendly Shape

Imagine a merchandising team uploads a spreadsheet where each row is a store and each month is a separate revenue column:

store	jan_revenue	feb_revenue	mar_revenue
East	12000	14000	13500
West	9000	11000	15000

That is fine for manual review. But suppose you now want to answer questions like:

• what was total revenue by month?
• which month was strongest across all stores?
• how did one store compare with the others over time?

Those are row-oriented questions. They become much easier once the data is reshaped into:

store	month	revenue
East	Jan	12000
East	Feb	14000
East	Mar	13500

That reshaping step is what UNPIVOT does.

The Core Idea: Wide Columns Become Repeated Rows

Conceptually, unpivoting is simple:

In portable SQL, UNION ALL is often the clearest way to express that transformation. Each SELECT pulls one original column, gives it a label, and stacks the results vertically.

Interactive Example 1: Turn Month Columns into Rows

Let’s start with the classic case: monthly revenue stored across separate columns.

This looks repetitive, but the result is far more useful than the original wide table.

Once the data is long rather than wide, standard SQL patterns become available again:

• GROUP BY month
• WHERE month IN (...)
• window functions by month
• joins to a calendar or dimension table

That is why unpivoting is often a data-preparation step, not just a formatting trick.

Why UNION ALL Instead of UNION?

Because unpivoting is supposed to preserve rows, not silently deduplicate them.

If two stores happen to have the same monthly revenue, that does not mean one of those rows should disappear. UNION ALL keeps every generated row exactly as intended.

This is a small but important rule:

• use UNION ALL for reshaping
• use UNION only when duplicate elimination is actually part of the business requirement

Interactive Example 2: Analyze the Long Result

The value of UNPIVOT is not the reshaping itself. The value is what becomes easier afterward.

Suppose finance wants one row per month with total revenue across all stores. That query is awkward against the wide input table, but straightforward once the data is converted to long form.

That second query is the real payoff. Once the data has the correct row shape, you can use normal grouping and ranking logic without fighting the original spreadsheet layout.

Native UNPIVOT Operators vs Portable SQL

Some databases offer a dedicated UNPIVOT clause. SQL Server and Oracle users may prefer it because it is concise and purpose-built.

But UNION ALL still has a few advantages:

• it is easy to read if the column set is small
• it works in more dialects
• it makes the generated label/value pairs explicit
• it is easier to customize when each source column needs different cleanup logic

That last point matters more than people expect. In real data prep, one month column might need COALESCE, another might need type casting, and another might need unit conversion. A manual UNION ALL pattern gives you space to express those differences directly.

Common UNPIVOT Use Cases

This pattern appears whenever a table is optimized for presentation rather than analysis:

• monthly columns in spreadsheet exports
• survey answers spread across repeated question columns
• KPI tables with one metric per column
• denormalized imports from CSV or Excel
• vendor reports where each period becomes a new field

If you catch yourself writing a query that compares jan_sales, feb_sales, and mar_sales side by side with repeated formulas, that is usually a sign the data wants to be unpivoted first.

Common Mistakes When Unpivoting

1. Mixing values with different meanings

Only stack columns together if they represent the same kind of measure.

For example, jan_revenue, feb_revenue, and mar_revenue belong together. But jan_revenue and customer_count should not be unpivoted into the same value column unless you also add a metric label and handle the mixed semantics carefully.

2. Forgetting null handling

If one source column is NULL, ask what that means:

• no value was recorded
• zero actually happened
• the source file was incomplete

Do not blindly COALESCE every null to zero unless the business meaning supports it.

3. Losing chronological order

Month labels such as 'Apr', 'Aug', and 'Dec' sort alphabetically unless you add a real sort key. In production queries, it is often safer to unpivot into a proper date or month number instead of display text only.

4. Unpivoting too late

If the next several steps of the workflow are all row-based analytics, reshape the table early. Otherwise the rest of the query becomes harder than it needs to be.

A Useful Variation: Add a Metric Name Column

Sometimes the source table is wide not across months, but across metric types:

• new_users
• active_users
• paying_users

The same pattern still works. Instead of a month label, you create a metric_name label and a metric_value column. That turns a dashboard-style summary into something you can filter and compare much more flexibly.

Boundary and Performance Notes

Manual unpivoting is straightforward, but there are still tradeoffs:

• wide source tables can require many UNION ALL branches
• if dozens of columns are involved, generated SQL may be easier to maintain
• unpivoting repeatedly in every downstream query may justify building a cleaned staging table instead
• mixed data types across source columns usually require explicit casting

So while UNION ALL is a strong default, the broader engineering question is whether the reshaped result should become part of your pipeline rather than something rebuilt ad hoc in every report.

Official References

• PostgreSQL combining queries documentation for the set-operation rules behind UNION ALL.
• SQLite compound SELECT documentation for SQLite behavior around stacked query branches.
• PostgreSQL CASE and conditional expressions for related cleanup patterns often used after unpivoting.

Related Articles

• How to Create Pivot Tables in SQL for the opposite transformation when you need to rotate rows into columns for reporting.
• Data Cleaning with SQL for the broader workflow of standardizing raw imports before analysis.
• SQL E-Commerce Analytics for practical reporting patterns that become easier once the dataset is at the right grain.

Conclusion

UNPIVOT is really a row-shape correction step.

If the source data is wide because it was designed for humans to read, reshape it into rows before you try to analyze it like a relational dataset. UNION ALL is simple, portable, and explicit, which makes it a strong default pattern for turning spreadsheet-shaped inputs into SQL-friendly tables.