Writing SQL to Unpivot Wide Tables into Long Format Using UNION ALL and Static Value Columns

Introduction

You've just received a dataset from the finance team. It's a spreadsheet-turned-database-table that tracks monthly revenue by product line, and it looks something like this: one row per product, with twelve columns named jan_revenue, feb_revenue, mar_revenue, and so on through dec_revenue. It's perfectly readable as a spreadsheet. It's nearly useless for SQL analysis.

You can't easily GROUP BY month. You can't filter to a date range. You can't join this structure against a calendar table or write a window function that moves across time. The data is wide when it needs to be tall — a classic mismatch between how humans like to read data and how SQL engines like to process it. The technique for fixing this is called unpivoting, and it's one of the most practically important transformations in applied SQL work.

By the end of this lesson, you'll be able to take any wide table and manually unpivot it into a clean long-format result using UNION ALL and static value columns. You'll understand why this technique works at the mechanical level, when to reach for it versus database-specific alternatives, and how to avoid the subtle mistakes that trip people up in production. You'll also build a complete working example from a realistic reporting scenario.

What you'll learn:

• What "wide" vs. "long" table formats mean and why the distinction matters for SQL analysis
• How UNION ALL creates long format by stacking rows instead of spreading values across columns
• How to inject static string or numeric values as synthetic columns to label what each row represents
• How to handle type mismatches and NULL values across heterogeneous source columns
• When to use the manual UNION ALL approach versus built-in unpivot syntax in specific databases

---

Prerequisites

This lesson assumes you're comfortable with the core SELECT syntax and can write multi-table queries. If you need a refresher on the fundamentals, check out SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing. You should also be familiar with how UNION ALL works as a set operation — if that's fuzzy, Combining Results with SQL Set Operations: UNION, UNION ALL, INTERSECT, and EXCEPT covers it in depth. Some exposure to GROUP BY will help in the exercises, but you don't need mastery of it to follow the core lesson.

---

The Problem with Wide Tables

Let's establish the vocabulary precisely, because "wide" and "long" get thrown around loosely.

A wide table stores multiple measurements for a single entity across separate columns. Each column represents a category of the measurement — in our case, a month. Here's what that looks like concretely:

-- The wide_product_revenue table
SELECT * FROM wide_product_revenue;

product_line	region	jan_revenue	feb_revenue	mar_revenue	apr_revenue
Software	North	142000	138500	155200	162000
Hardware	North	88000	91200	87500	93100
Services	North	54000	56000	61200	58900
Software	South	97000	103000	115000	121500

A long table stores the same data with one row per measurement, with separate columns for the category label and the value. The same data in long format looks like this:

product_line	region	month	revenue
Software	North	January	142000
Software	North	February	138500
Software	North	March	155200
Software	North	April	162000
Hardware	North	January	88000
...	...	...	...

Each row in the wide table becomes four rows in the long table (one per month). The wide table had four measurement columns; those four columns collapse into two columns: month (the label) and revenue (the value).

If you've ever tried to write a GROUP BY month on a wide table, you've felt this friction directly. There's no month column to group by — the month information is encoded in the column names, not in the data. Unpivoting moves that information from the schema into the rows where SQL can actually use it.

The other painful consequence of wide tables is that adding a new measurement category — say, a new month or a new product attribute — requires an ALTER TABLE to add a column. In long format, you just insert new rows. This is covered in the SQL Data Types and Schema Design Mastery lesson if you want to explore schema design tradeoffs further.

---

How UNION ALL Creates Long Format

The core insight behind manual unpivoting is this: if you write one SELECT per column you want to unpivot, and stack those selects with UNION ALL, each original row spawns N rows — one per column.

Let's start with the simplest possible version. Suppose we only have two months to unpivot:

-- Two-month unpivot: the building block
SELECT
    product_line,
    region,
    'January'       AS month,
    jan_revenue     AS revenue
FROM wide_product_revenue

UNION ALL

SELECT
    product_line,
    region,
    'February'      AS month,
    feb_revenue     AS revenue
FROM wide_product_revenue;

Walk through what's happening here:

1. The first SELECT reads every row in wide_product_revenue and produces a result where month is literally the string 'January' and revenue comes from the jan_revenue column.
2. The second SELECT does exactly the same, but uses 'February' and feb_revenue.
3. UNION ALL stacks these two result sets. Because UNION ALL doesn't deduplicate (unlike UNION), every row from both queries appears in the final output.

The string 'January' is what we mean by a static value column — a literal value you write directly into the SELECT list that doesn't come from any column in the source table. It's the mechanism that allows you to inject the column name as actual data. This is the whole trick: you're doing by hand what the database's column headers were doing implicitly.

---

A Full 12-Month Unpivot

Let's write the complete query for a full twelve-month unpivot. This is mechanical but important to see in full — you'll understand why optimizations and shortcuts matter once you've done it the long way:

SELECT product_line, region, 'January'   AS month, jan_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'February'  AS month, feb_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'March'     AS month, mar_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'April'     AS month, apr_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'May'       AS month, may_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'June'      AS month, jun_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'July'      AS month, jul_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'August'    AS month, aug_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'September' AS month, sep_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'October'   AS month, oct_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'November'  AS month, nov_revenue   AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 'December'  AS month, dec_revenue   AS revenue FROM wide_product_revenue;

If your source table has 4 product/region combinations, this query returns 48 rows (4 × 12). If it has 1,000 rows, you get 12,000.

Notice that the formatting here matters for readability. Each SELECT block is kept on one line and visually aligned with the others. When you're writing a 20-column unpivot at 2am debugging a data pipeline, alignment is what saves you from mismatching a label and a column. Take the 90 seconds to format it cleanly.

---

Injecting Numeric Position Columns

Month names as strings are fine for display, but they're terrible for sorting and joining. 'April' < 'August' alphabetically, which is not the order you want on a time series chart. The fix is to add a numeric position column alongside the label:

SELECT product_line, region, 1  AS month_num, 'January'   AS month_name, jan_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 2  AS month_num, 'February'  AS month_name, feb_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 3  AS month_num, 'March'     AS month_name, mar_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 4  AS month_num, 'April'     AS month_name, apr_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 5  AS month_num, 'May'       AS month_name, may_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 6  AS month_num, 'June'      AS month_name, jun_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 7  AS month_num, 'July'      AS month_name, jul_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 8  AS month_num, 'August'    AS month_name, aug_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 9  AS month_num, 'September' AS month_name, sep_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 10 AS month_num, 'October'   AS month_name, oct_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 11 AS month_num, 'November'  AS month_name, nov_revenue AS revenue FROM wide_product_revenue UNION ALL
SELECT product_line, region, 12 AS month_num, 'December'  AS month_name, dec_revenue AS revenue FROM wide_product_revenue;

Now you can ORDER BY month_num to get a correct time series, or WHERE month_num BETWEEN 3 AND 8 to filter to Q2 and Q3. You can also join this result to a calendar dimension table on month_num to pull in days-in-month, quarter labels, fiscal period, or anything else your reporting layer needs.

---

Wrapping the Unpivot in a CTE or Subquery

Raw unpivot queries are rarely the final product. Usually you want to filter, aggregate, or join on the long-format output. The cleanest pattern is to wrap the unpivot in a CTE so your analytical query stays readable. CTE patterns are covered in depth in Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture, but here's the pattern applied to our scenario:

WITH monthly_revenue AS (
    SELECT product_line, region, 1  AS month_num, 'January'   AS month_name, jan_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 2  AS month_num, 'February'  AS month_name, feb_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 3  AS month_num, 'March'     AS month_name, mar_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 4  AS month_num, 'April'     AS month_name, apr_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 5  AS month_num, 'May'       AS month_name, may_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 6  AS month_num, 'June'      AS month_name, jun_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 7  AS month_num, 'July'      AS month_name, jul_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 8  AS month_num, 'August'    AS month_name, aug_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 9  AS month_num, 'September' AS month_name, sep_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 10 AS month_num, 'October'   AS month_name, oct_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 11 AS month_num, 'November'  AS month_name, nov_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 12 AS month_num, 'December'  AS month_name, dec_revenue AS revenue FROM wide_product_revenue
)
-- Now the analysis query operates on clean long-format data
SELECT
    product_line,
    month_name,
    SUM(revenue)                                        AS total_revenue,
    ROUND(AVG(revenue), 0)                              AS avg_revenue_per_region
FROM monthly_revenue
WHERE month_num BETWEEN 1 AND 6          -- H1 only
GROUP BY product_line, month_num, month_name
ORDER BY product_line, month_num;

The CTE gives the unpivot result a name (monthly_revenue) and lets the downstream query treat it exactly like a regular table. Notice the GROUP BY now includes month_num for correct ordering, even though month_num doesn't appear in SELECT. This is standard — you're using it as an ordering anchor in ORDER BY. Check out Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization if you want to go deeper on how GROUP BY and ORDER BY interact in aggregate queries.

---

Unpivoting Multiple Measure Columns Simultaneously

Things get more interesting when your wide table has more than one measure per category. Imagine the revenue table also tracks unit count per month:

-- A wider source table with two measures per month
CREATE TABLE wide_product_metrics (
    product_line   VARCHAR(50),
    region         VARCHAR(50),
    jan_revenue    NUMERIC(12,2),
    jan_units      INTEGER,
    feb_revenue    NUMERIC(12,2),
    feb_units      INTEGER,
    mar_revenue    NUMERIC(12,2),
    mar_units      INTEGER
    -- ... and so on
);

You have a choice here about what your long format looks like. Option A is to keep both measures as separate columns in the long result — one revenue column and one units column, with one row per product/region/month:

-- Option A: Multiple measures as separate columns (one row per month)
WITH monthly_metrics AS (
    SELECT product_line, region, 1 AS month_num, 'January' AS month_name,
           jan_revenue AS revenue, jan_units AS units
    FROM wide_product_metrics

    UNION ALL

    SELECT product_line, region, 2 AS month_num, 'February' AS month_name,
           feb_revenue AS revenue, feb_units AS units
    FROM wide_product_metrics

    UNION ALL

    SELECT product_line, region, 3 AS month_num, 'March' AS month_name,
           mar_revenue AS revenue, mar_units AS units
    FROM wide_product_metrics
)
SELECT
    product_line,
    month_name,
    SUM(revenue)                            AS total_revenue,
    SUM(units)                              AS total_units,
    ROUND(SUM(revenue) / NULLIF(SUM(units), 0), 2) AS avg_revenue_per_unit
FROM monthly_metrics
GROUP BY product_line, month_num, month_name
ORDER BY product_line, month_num;

This is usually the right choice when your measures naturally belong together on the same row (revenue and units for the same month are intrinsically related). The calculated column avg_revenue_per_unit shows why: you can only divide revenue by units correctly if they appear on the same row.

Option B is a fully-normalized long format with a measure_name and measure_value column — essentially unpivoting both the month dimension and the measure dimension. This is the approach some BI tools and statistical packages prefer:

-- Option B: Fully normalized (two rows per month: one for revenue, one for units)
SELECT product_line, region, 1, 'January', 'revenue', jan_revenue FROM wide_product_metrics UNION ALL
SELECT product_line, region, 1, 'January', 'units',   jan_units   FROM wide_product_metrics UNION ALL
SELECT product_line, region, 2, 'February','revenue', feb_revenue FROM wide_product_metrics UNION ALL
SELECT product_line, region, 2, 'February','units',   feb_units   FROM wide_product_metrics
-- ... and so on

The tradeoff is real: Option B is more flexible for tools that need to pivot dynamically, but computing revenue / units now requires a self-join or conditional aggregation — extra complexity for every downstream query. Choose based on how your consumers will actually use the data.

---

Handling NULL Values in Source Columns

Wide tables that come from spreadsheets often have NULL values sprinkled throughout. A product that launched in March will have NULL for January and February revenue. When you unpivot, those NULL values become NULL rows in the long format — which is correct behavior. You want those rows there to represent that the measurement period exists but has no value.

What you don't want is to silently drop those rows, which is a tempting but wrong impulse when cleaning data. Here's the difference:

-- WRONG: Filtering NULLs during unpivot creates gaps in your time series
WITH monthly_revenue AS (
    SELECT product_line, region, 1, 'January', jan_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 2, 'February', feb_revenue AS revenue FROM wide_product_revenue
    -- ... etc
)
SELECT * FROM monthly_revenue
WHERE revenue IS NOT NULL;  -- This drops legitimate "no revenue" observations!

-- CORRECT: Keep NULLs in long format; handle them explicitly when aggregating
WITH monthly_revenue AS (
    SELECT product_line, region, 1, 'January', jan_revenue AS revenue FROM wide_product_revenue UNION ALL
    SELECT product_line, region, 2, 'February', feb_revenue AS revenue FROM wide_product_revenue
    -- ... etc
)
SELECT
    product_line,
    month_name,
    COALESCE(SUM(revenue), 0) AS total_revenue,   -- NULL becomes 0 at aggregation time
    COUNT(revenue)             AS months_with_data  -- Counts only non-NULL values
FROM monthly_revenue
GROUP BY product_line, month_num, month_name
ORDER BY product_line, month_num;

The COALESCE and COUNT approach gives you both: the accurate total (treating no revenue as zero) and the count of months where you actually had data, which lets you distinguish "zero revenue" from "not yet launched." See NULL Handling in SQL: IS NULL, COALESCE, and NULLIF for a thorough treatment of these patterns.

---

Unpivoting a Survey Results Table: A Realistic Project

Let's build something you might actually need to produce. Your company runs a quarterly customer satisfaction survey. Each respondent rates four dimensions on a 1–10 scale: product_quality, support_quality, delivery_speed, and value_for_money. The results are stored wide:

CREATE TABLE survey_responses (
    response_id      INTEGER PRIMARY KEY,
    survey_quarter   VARCHAR(10),    -- e.g. 'Q1-2024'
    customer_segment VARCHAR(50),    -- e.g. 'Enterprise', 'SMB', 'Consumer'
    product_quality  SMALLINT,       -- 1-10 scale
    support_quality  SMALLINT,
    delivery_speed   SMALLINT,
    value_for_money  SMALLINT
);

Goal: Produce a report showing, for each customer segment and survey dimension, the average score, the percentage of responses that scored 8 or above (your internal "promoter" threshold), and the number of responses. We want this broken down by quarter so we can track trends.

Here's the complete solution:

WITH long_responses AS (
    -- Unpivot the four dimension columns into rows
    SELECT
        response_id,
        survey_quarter,
        customer_segment,
        'Product Quality'  AS dimension,
        product_quality    AS score
    FROM survey_responses

    UNION ALL

    SELECT
        response_id,
        survey_quarter,
        customer_segment,
        'Support Quality'  AS dimension,
        support_quality    AS score
    FROM survey_responses

    UNION ALL

    SELECT
        response_id,
        survey_quarter,
        customer_segment,
        'Delivery Speed'   AS dimension,
        delivery_speed     AS score
    FROM survey_responses

    UNION ALL

    SELECT
        response_id,
        survey_quarter,
        customer_segment,
        'Value for Money'  AS dimension,
        value_for_money    AS score
    FROM survey_responses
),
scored AS (
    -- Add derived columns before aggregation
    SELECT
        survey_quarter,
        customer_segment,
        dimension,
        score,
        CASE WHEN score >= 8 THEN 1 ELSE 0 END AS is_promoter
    FROM long_responses
    WHERE score IS NOT NULL   -- Respondents who skipped a question
)
SELECT
    survey_quarter,
    customer_segment,
    dimension,
    COUNT(*)                                    AS response_count,
    ROUND(AVG(score::NUMERIC), 2)               AS avg_score,
    ROUND(100.0 * SUM(is_promoter) / COUNT(*), 1) AS promoter_pct
FROM scored
GROUP BY survey_quarter, customer_segment, dimension
ORDER BY survey_quarter, customer_segment, dimension;

A few things worth highlighting in this query:

The CTE chain. We use two CTEs: long_responses does the unpivot and nothing else; scored adds the is_promoter derivation. Keeping these concerns separate makes the query much easier to modify — if the promoter threshold changes from 8 to 9, you change exactly one number in exactly one place. The Writing Reusable SQL Column Logic lesson covers this principle more broadly.

The CASE WHEN for promoter flag. We're using CASE expressions to convert the boolean condition (score >= 8) into a 1/0 integer before aggregation. SUM(is_promoter) / COUNT(*) is a standard pattern for computing "percentage of rows meeting a condition" without needing a subquery.

WHERE score IS NOT NULL in the second CTE. We're filtering nulls after the unpivot and before aggregation. This is intentional — we want to exclude respondents who skipped a question from that dimension's statistics, without excluding their responses to other dimensions.

The cast score::NUMERIC. AVG() on integer columns can return an integer result in some databases (notably PostgreSQL without an explicit cast), which would truncate the decimal. The ::NUMERIC syntax (PostgreSQL) or CAST(score AS NUMERIC) (ANSI) forces a decimal result.

---

Database-Specific Unpivot Alternatives

The manual UNION ALL approach works in every SQL database that has ever existed. But some databases offer built-in syntax that can replace the boilerplate for simple cases:

SQL Server: UNPIVOT operator

SELECT product_line, region, month, revenue
FROM wide_product_revenue
UNPIVOT (
    revenue FOR month IN (
        jan_revenue, feb_revenue, mar_revenue, apr_revenue
    )
) AS unpvt;

This is more concise but has significant limitations: it drops NULL values silently by default (a subtle trap), you can't rename the output values to human-readable labels without an extra step, and it only handles one measure at a time.

Snowflake / dbt environments: Snowflake has a UNPIVOT function with similar syntax. In dbt, the unpivot macro in the dbt_utils package generates the UNION ALL boilerplate programmatically from a list of column names.

BigQuery / Spark SQL: These have UNPIVOT available in recent versions, but syntax varies. The UNION ALL approach is portable across all of them without modification.

The place where native UNPIVOT syntax genuinely wins is when you have 50+ columns to unpivot and the column list is dynamic or generated. At that point, you typically need a code-generation approach regardless of which path you take — either programmatic SQL generation or a macro system.

---

Hands-On Exercise

Setup: Create and populate this table in your SQL environment:

CREATE TABLE quarterly_headcount (
    department      VARCHAR(50),
    cost_center     VARCHAR(20),
    q1_headcount    INTEGER,
    q2_headcount    INTEGER,
    q3_headcount    INTEGER,
    q4_headcount    INTEGER,
    q1_budget_usd   NUMERIC(10,2),
    q2_budget_usd   NUMERIC(10,2),
    q3_budget_usd   NUMERIC(10,2),
    q4_budget_usd   NUMERIC(10,2)
);

INSERT INTO quarterly_headcount VALUES
    ('Engineering',  'CC-101', 42, 45, 48, 51,  2100000, 2250000, 2400000, 2550000),
    ('Marketing',    'CC-102', 18, 20, 20, 22,   900000, 1000000, 1000000, 1100000),
    ('Sales',        'CC-103', 55, 58, 62, 65,  2750000, 2900000, 3100000, 3250000),
    ('Operations',   'CC-104', 30, 30, 28, 29,  1500000, 1500000, 1400000, 1450000),
    ('Finance',      'CC-105', 12, 12, 13, 13,   600000,  600000,  650000,  650000);

Tasks:

1. Write a UNION ALL unpivot query that produces one row per department per quarter, with columns: department, cost_center, quarter_num (1-4), quarter_label (e.g., 'Q1 2024'), headcount, and budget_usd.

2. Wrap your unpivot in a CTE and write an analysis query that computes, per department: total annual headcount (sum across quarters is intentionally redundant here — use average to get a snapshot), total annual budget, and cost per head per quarter.

3. Extend the analysis to flag departments where any quarter's cost-per-head exceeds $55,000. Use a HAVING clause on the per-department aggregate. The Filtering Groups After Aggregation lesson covers HAVING patterns in depth.

4. Stretch goal: Modify the unpivot so that instead of one row per department per quarter, you have two rows per department per quarter — one for headcount and one for budget — with a metric_name and metric_value column. Handle the type difference between INTEGER and NUMERIC explicitly.

Expected output for Task 1 (first few rows):

department	cost_center	quarter_num	quarter_label	headcount	budget_usd
Engineering	CC-101	1	Q1 2024	42	2100000.00
Engineering	CC-101	2	Q2 2024	45	2250000.00
Engineering	CC-101	3	Q3 2024	48	2400000.00
Engineering	CC-101	4	Q4 2024	51	2550000.00
Marketing	CC-102	1	Q1 2024	18	900000.00

---

Common Mistakes & Troubleshooting

Mistake 1: Column count or type mismatch across UNION ALL branches

UNION ALL requires that every SELECT in the stack has the same number of columns, in the same order, with compatible types. If you accidentally write 6 columns in one branch and 7 in another, you'll get an error like each UNION query must have the same number of columns.

The subtle version is a type mismatch: one branch selects a VARCHAR column where another selects a NUMERIC. Some databases (MySQL, BigQuery) will silently cast; others (PostgreSQL, SQL Server) will throw an error. The fix is to be explicit with CAST() wherever types might differ.

Mistake 2: Misaligning labels and source columns

This is the most common logical error in unpivot queries and the hardest to catch because it produces no SQL error — just wrong data. If you write:

SELECT product_line, region, 'January' AS month, feb_revenue AS revenue   -- BUG: label says January, column is February
FROM wide_product_revenue

...you'll get a result where January rows contain February's numbers. With twelve months and careful formatting, it's easy to drift one row out of alignment. The fix is alignment and code review: keep the label and the column name on the same line and in a consistent visual pattern.

Mistake 3: Using UNION instead of UNION ALL

A UNION query deduplicates by scanning the full result set. On a 12-branch unpivot of a table with 100,000 rows, you're asking the database to sort and deduplicate 1.2 million rows — work that accomplishes nothing useful, since correctly written unpivot branches never produce identical rows (they differ at minimum in the static label column).

Always use UNION ALL. If you're ever tempted to use UNION to remove actual duplicates that appeared post-unpivot, something has gone wrong upstream — investigate rather than patch.

Mistake 4: Forgetting to carry through all identifier columns

Wide tables often have multiple identifier columns: a product ID, a region, a channel, a customer segment. When writing each UNION ALL branch, every branch must include all identifier columns, not just the ones you happen to need for the current query. If you omit an identifier from one branch, UNION ALL will still run — it'll just show NULL for that column in those rows, silently corrupting your result.

Review your SELECT column list once per branch and confirm it's identical to every other branch (except the static label and the value column, which change intentionally).

Mistake 5: Reading the query without testing intermediate CTEs

When debugging a multi-CTE unpivot query, run just the unpivot CTE first. Add a SELECT * FROM cte_name LIMIT 20 after it and confirm the shape looks right before writing the analysis layer. This is basic query debugging discipline — isolate the components before composing them. The Debugging SQL Queries lesson covers this methodology systematically.

---

Performance Considerations

The UNION ALL unpivot reads the source table once per branch. A 12-branch unpivot scans the source table 12 times. For small to medium tables (say, under a few million rows), this is typically not a problem — the database optimizer will often cache or buffer the reads. But for large tables, you should be aware that you're multiplying your scan cost.

Mitigation options:

• Filter before unpivoting. Add a WHERE clause inside each SELECT branch if you only need a subset of rows. The filter runs against the original table, reducing what gets scanned and stacked.
• Create a staging table. For scheduled ETL/ELT jobs, it's often better to write the unpivoted result into a staging or intermediate table once, then run all downstream queries against that. This separates the transformation cost from the query cost.
• Partial indexes or partitioning. If the wide table is partitioned by region or time, the database can use partition pruning to reduce what each branch reads. See SQL Indexes Explained: How They Work and When to Create Them for background.

For analytical databases like BigQuery, Snowflake, and Redshift, columnar storage means each branch scan only reads the specific columns it needs rather than the full row. A 12-branch unpivot on a 20-column table reads only 3 columns per branch (the identifiers plus the value column), making it much cheaper than it looks on a row-oriented system.

---

Pivoting as the Inverse Operation

It's worth understanding that unpivoting is the exact inverse of the SQL pivot pattern. If you've used CASE WHEN and GROUP BY to turn rows into columns, you've seen how long format gets condensed back into wide format for display. Many reporting workflows follow the full cycle: data arrives wide → unpivot to long for analysis → pivot back to wide for the final report. Understanding both directions gives you full control of the shape of your data at every stage.

---

Summary & Next Steps

Manual unpivoting with UNION ALL and static value columns is a foundational transformation skill for anyone working with real-world data. Data collected in spreadsheets, exported from legacy systems, or staged in flat files almost always arrives in wide format. SQL analysis almost always wants long format. The UNION ALL pattern bridges that gap with no special syntax, no database-specific features, and complete transparency about what's happening.

The key ideas to take away:

• Wide tables store measurements in columns; long tables store them in rows. SQL's row-oriented operations (GROUP BY, WHERE, JOINs, window functions) need long format.
• UNION ALL stacks the results of multiple SELECT statements. Write one SELECT per column you're unpivoting, and you get one row per column per original row.
• Static value columns are literals written directly into the SELECT list ('January' AS month_name, 1 AS month_num). They inject the column-name information as actual data.
• Wrap the unpivot in a CTE so the analysis query operates on a named, clean long-format result.
• Always use UNION ALL, not UNION. Always carry all identifier columns through every branch. Always sanity-check totals.

Where to go next:

• If you're working with complex multi-step reporting after unpivoting, Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY shows how to compose these patterns into complete analytical solutions.
• If you need to go in the other direction — turning long-format data back into a wide report — study Pivoting Query Results in SQL: Using CASE WHEN and GROUP BY to Turn Rows into Columns.
• For more advanced transformations involving conditional logic inside your unpivot columns, Writing SQL CASE Expressions: Conditional Logic Inside SELECT, WHERE, and GROUP BY gives you the full toolkit.