1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL unpivot turns wide tables, with one column per month or quarter, into long tables with one row per value, which is the shape that grouping, charts and joins need. We compared UNION ALL, a CROSS JOIN with CASE, a JSON trick and native UNPIVOT on the same quarterly sales data, checked how each handles NULLs, and timed them on 600,000 output rows.

9 min read
SQL UNPIVOT: turn columns into rows, 4 ways tested

Key takeaways

  • SQL unpivot converts columns such as q1, q2, q3, q4 into rows with a label column and a value column.
  • UNION ALL works in every database; our three regions became 12 rows, and the 3 NULL cells were kept.
  • Adding up the wide columns with q1 + q2 + q3 + q4 returned NULL for two of three regions; summing the long table gave the right total, 1,255.
  • On 50,000 rows by 12 months, UNION ALL took 0.42 s and CROSS JOIN with CASE 0.40 s, but UNION ALL scanned the table 12 times.

Spreadsheets, survey exports and reporting tables often arrive wide: one row per region with columns q1, q2, q3, q4, or one row per product with twelve month columns. They are easy to read and hard to query. SQL unpivot reshapes them into a long table with one row per region and quarter, which makes averages, filters, charts and joins straightforward.

All queries ran on SQLite 3.50.4. SQLite has no UNPIVOT keyword, which is a good test of the portable methods. Native UNPIVOT syntax for SQL Server and Oracle is shown from their documentation; we did not run those databases.

What unpivot means

Wide:   region | q1  | q2  | q3  | q4
        North  | 120 | 135 |     | 160

Long:   region | quarter | amount
        North  | q1      | 120
        North  | q2      | 135
        North  | q3      | NULL
        North  | q4      | 160

Unpivoting is the reverse of pivoting. The column names become values in a new label column, and the column contents become a single value column.

Our wide table

CREATE TABLE sales (region TEXT, q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO sales VALUES
  ('North', 120, 135, NULL, 160),
  ('South',  90,  95, 110,  105),
  ('West',  200, NULL, NULL, 240);

The NULLs are deliberate. North has no Q3 figure yet and West is missing two quarters, which is typical of real exports.

Why convert to long format?

SELECT region, q1 + q2 + q3 + q4 FROM sales;
-- [('North', None), ('South', 400), ('West', None)]

Even a simple yearly total goes wrong on the wide table: arithmetic with a NULL gives NULL, so two of three regions had no total. You can patch it with COALESCE on every column, but every new question (average per quarter, best quarter, quarters above target) needs a new hand-written expression across all columns. In long format these become ordinary GROUP BY queries, and adding a fifth quarter needs no query changes at all.

Method 1: UNION ALL

SELECT region, 'q1' AS quarter, q1 AS amount FROM sales
UNION ALL SELECT region, 'q2', q2 FROM sales
UNION ALL SELECT region, 'q3', q3 FROM sales
UNION ALL SELECT region, 'q4', q4 FROM sales
ORDER BY region, quarter;
-- 12 rows, starting ('North', 'q1', 120), ('North', 'q2', 135), ('North', 'q3', None), ('North', 'q4', 160)

One SELECT per column, each with a literal label, glued together with UNION ALL. It runs on every SQL database, including MySQL, which has no UNPIVOT. Use UNION ALL, not UNION: plain UNION removes duplicate rows, so two regions with the same amount in the same quarter would silently merge, as explained in our UNION vs UNION ALL guide. The downside is repetition, and as the plan shows later, one table scan per column.

Method 2: CROSS JOIN with CASE

WITH k(quarter) AS (VALUES ('q1'), ('q2'), ('q3'), ('q4'))
SELECT s.region, k.quarter,
       CASE k.quarter WHEN 'q1' THEN s.q1 WHEN 'q2' THEN s.q2
                      WHEN 'q3' THEN s.q3 ELSE s.q4 END AS amount
FROM sales s CROSS JOIN k
WHERE amount IS NOT NULL
ORDER BY s.region, k.quarter;
-- 9 rows (the 3 NULL cells removed by the WHERE)

Join every source row with a small list of labels, then pick the matching column with CASE. The table is read once, and the labels live in one place. In PostgreSQL the same idea is usually written CROSS JOIN LATERAL (VALUES ('q1', s.q1), ('q2', s.q2), ...) AS v(quarter, amount), which avoids the CASE entirely. SQLite rejected the inline form (VALUES ...) AS k(quarter) with a syntax error, so we declared the labels in a CTE instead.

Method 3: JSON functions

SELECT s.region, j.key AS quarter, j.value AS amount
FROM sales s,
     json_each(json_object('q1', s.q1, 'q2', s.q2, 'q3', s.q3, 'q4', s.q4)) j
ORDER BY 1, 2;
-- 12 rows, starting ('North', 'q1', 120), ('North', 'q2', 135)

Pack the columns into a JSON object, then expand its keys and values back into rows with a table-valued function. In SQLite that is json_each; PostgreSQL has jsonb_each and MySQL JSON_TABLE. It is handy when the column list is long or generated, and it keeps NULLs. Values pass through JSON, so check types afterwards: dates become strings. More in our SQL JSON functions guide.

Method 4: native UNPIVOT (SQL Server, Oracle, Snowflake)

-- SQL Server / Oracle syntax, not run in this test
SELECT region, quarter, amount
FROM sales
UNPIVOT (amount FOR quarter IN (q1, q2, q3, q4)) AS u;

Some databases have an UNPIVOT operator that does all of this in one clause: name the new value column, the new label column and the source columns. It is the shortest form, but not portable. Its documented NULL behaviour differs from the other methods: SQL Server’s UNPIVOT drops NULL cells, and Oracle drops them by default unless you write UNPIVOT INCLUDE NULLS. Snowflake and DuckDB also support UNPIVOT.

The NULL question: keep or drop?

Method NULL cells
UNION ALL Kept (12 rows in our test)
CROSS JOIN with CASE Kept unless filtered (we filtered to 9)
JSON functions Kept (12 rows)
Native UNPIVOT Dropped by default (documented)

Neither choice is always right. A NULL may mean “zero”, “not reported yet” or “not applicable”, and that changes averages: AVG ignores NULLs, so a missing quarter is excluded rather than counted as zero. In our long table the Q2 average was 115.0 over 2 values, not 76.7 over 3. Decide what a missing value means, then keep, drop or COALESCE it deliberately; see our SQL NULL guide.

Checking the result

-- wide total (with COALESCE)   (1255,)
-- long total                    (1255,)
SELECT quarter, AVG(amount), COUNT(amount) FROM long GROUP BY quarter;
-- [('q1', 136.67, 3), ('q2', 115.0, 2), ('q3', 110.0, 1), ('q4', 168.33, 3)]

Always reconcile after reshaping: the grand total of the long table must equal the sum of every wide column, and the row count must equal source rows times columns (minus any NULLs you dropped). Both totals were 1,255 here. Then queries that were awkward become one-liners with GROUP BY.

Performance

-- 50,000 rows x 12 month columns = 600,000 output rows
union all x12:      0.42 s   plan: 12 table scans
cross join + case:  0.40 s   plan: 1 scan of the table (plus the 12-row label list)

On SQLite the two methods finished in about the same time, but the query plans differ: UNION ALL read the whole table once per column, while the CROSS JOIN read it once. On a small in-memory table that barely matters; on a large table on disk, or one that is expensive to compute (a view or a filtered join), reading it twelve times can be far slower. If the source is costly, unpivot from a CTE that is materialised once, or use the CROSS JOIN or native form. Measure with your own database’s plan; our EXPLAIN plan guide shows how to read one.

Pivoting back

SELECT region,
       SUM(CASE WHEN quarter = 'q1' THEN amount END) AS q1,
       SUM(CASE WHEN quarter = 'q4' THEN amount END) AS q4
FROM long GROUP BY region;
-- [('North', 120, 160), ('South', 90, 105), ('West', 200, 240)]

Conditional aggregation turns the long table back into columns for a report. Keeping data long and pivoting only for display is usually the cleanest design.

Wide vs long: which should you store?

  • Store long for anything that grows over time (months, sensors, survey questions): new periods are new rows, not schema changes.
  • Store wide only for a small, fixed set of attributes that are always read together.
  • Unpivot on import when spreadsheets arrive wide, then load the long result with INSERT INTO SELECT.
  • Index the long table on the label and entity columns you filter by most.

Final thoughts

SQL unpivot is a small reshaping step that makes every later query simpler. UNION ALL is the universal method, CROSS JOIN with CASE or LATERAL VALUES reads the table once, JSON functions handle long or generated column lists, and native UNPIVOT is the shortest where it exists. Whichever you choose, decide deliberately what happens to NULLs and reconcile totals afterwards. In our test, the long table answered a yearly-total question that the wide table got wrong for two regions out of three.

Frequently asked questions

What does UNPIVOT do in SQL?

It turns columns into rows. Each selected column becomes a row with a label column holding the old column name and a value column holding its contents.

How do I unpivot in MySQL or SQLite?

Neither has an UNPIVOT keyword. Use one SELECT per column combined with UNION ALL, or a cross join with a list of labels and a CASE expression.

Does UNPIVOT keep NULL values?

Native UNPIVOT in SQL Server drops them, and Oracle drops them unless you add INCLUDE NULLS. The UNION ALL and cross join methods keep them unless you filter them out.

Should I use UNION or UNION ALL to unpivot?

UNION ALL. Plain UNION removes duplicate rows, which would merge different entities that happen to have the same value in the same period.

What is the difference between PIVOT and UNPIVOT?

PIVOT turns row values into columns, usually with an aggregate. UNPIVOT does the reverse, turning columns back into rows, which is the better format for storing and querying data.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →