Unpivoting Columns Back Into Rows in SQL for Excel Data

A spreadsheet that has a column for every month looks tidy to read and is awkward to query. Averages by quarter, a filter on one month, a chart with months on an axis: each needs the months in a single column, not twelve. Moving from the wide layout to the long one is unpivoting. This post shows how to do it in the main SQL dialects, how to handle nulls, and what to check afterwards. It is a spoke of pivot tables and their SQL equivalents.

The wide table

An Excel sheet loaded into a table budget:

The long form wanted for analysis:

Each input row produces three output rows, one for each month column. The identifying column dept is repeated.

PostgreSQL: LATERAL and VALUES

The clearest form uses a lateral join to a VALUES list, so each source row is joined to a small table of its own columns:

SELECT b.dept, v.month, v.amount
FROM budget b
CROSS JOIN LATERAL (VALUES
    ('jan', b.jan),
    ('feb', b.feb),
    ('mar', b.mar)
) AS v(month, amount)
ORDER BY b.dept DESC, v.month;

LATERAL lets the VALUES list refer to the current row of budget. Each row of the list becomes an output row, and the null for Sales in March is kept as a row with a null amount. Because the list is an ordinary expression list, you can mix types by casting, compute values (b.jan * 1.1) or add labels that are not column names. Add as many pairs as you have columns.

SQL Server: UNPIVOT and CROSS APPLY

SQL Server has the UNPIVOT operator:

SELECT dept, month, amount
FROM budget
UNPIVOT (amount FOR month IN (jan, feb, mar)) AS u;

The behavior that bites is that UNPIVOT drops rows where the value is null. The Sales March row from above is not in the output. If a missing month is information, for example "no budget set", you lose it without a message, and the count of output rows is not rows × columns. The CROSS APPLY (VALUES ...) form is the lateral equivalent and keeps nulls:

SELECT b.dept, v.month, v.amount
FROM budget b
CROSS APPLY (VALUES ('jan', b.jan), ('feb', b.feb), ('mar', b.mar)) AS v(month, amount);

UNPIVOT also requires the columns being unpivoted to share a type. Mixed types fail with a conversion error, where the VALUES form lets you cast each one.

MySQL and others: UNION ALL

MySQL has neither UNPIVOT nor LATERAL VALUES in the same form. The portable method is one SELECT per column, joined with UNION ALL:

SELECT dept, 'jan' AS month, jan AS amount FROM budget
UNION ALL
SELECT dept, 'feb', feb FROM budget
UNION ALL
SELECT dept, 'mar', mar FROM budget;

It works everywhere. The cost is that the table is read once per column. For three columns that is nothing; for sixty columns on a large table it adds up, though the reads are often cached. MySQL 8 supports JSON_TABLE, which can unpivot in one pass through a JSON object built from the row, but the UNION ALL is easier to read and debug. Use UNION ALL, not UNION, because UNION removes duplicate rows and would merge two months with identical amounts.

DuckDB: UNPIVOT

DuckDB supports UNPIVOT as a statement, including a dynamic column choice, which is its advantage when the sheet is read straight from a file:

UNPIVOT (SELECT * FROM read_xlsx('budget.xlsx'))
ON COLUMNS(* EXCLUDE (dept))
INTO NAME month VALUE amount;

COLUMNS(* EXCLUDE (dept)) selects every column except the key, so a new month column in next year's file is unpivoted without a code change. DuckDB's UNPIVOT also drops null values by default; add INCLUDE NULLS to keep them (loading Excel files with DuckDB). Check the documentation for your version, since read_xlsx support depends on the release.

Nulls: drop or keep

Decide what a null cell means before choosing the method.

• No data. A blank month because nothing happened can be dropped. Many reports want only observed values.

• Zero. If blank means zero, keep the row and COALESCE(amount, 0).

• Not yet known. A forecast column that has not been filled is a gap you may want to see in the long table.

Write the decision into the query so the next person does not discover it by accident. The difference between UNPIVOT and LATERAL VALUES on nulls has produced many "why are my row counts different" threads.

Cleaning the key column after

Month names that came from column headers are text: jan, Jan 2025, 2025-01. After unpivoting, convert to a real date so the long table can be sorted and grouped:

SELECT dept,
       to_date(month || ' 2025', 'Mon YYYY') AS month_start,
       amount
FROM long_budget;

Headers carry stray spaces and different capitals. Trim and normalize them before they become data (trimming spaces and non breaking spaces).

Checks

Count rows: the long table should have rows × columns rows when nulls are kept, or that minus the number of null cells when they are dropped. Compare totals too: SELECT SUM(jan + feb + mar) FROM budget (with COALESCE) against SELECT SUM(amount) FROM long. Both should match.

For a recurring job, assert it. A test that fails when the count differs catches a new column that the query does not mention. The VALUES list is explicit, so a column added to the sheet next year is silently left out until someone updates the list.

Several measures per column

Some sheets hold two measures per month, such as budget and actual, side by side as jan_budget, jan_actual, feb_budget and feb_actual. A single unpivot to two columns of names and values would put budget and actual in the same column, which is not what you want. Unpivot to a long form with the measure name as a column, then split it, or write the VALUES list with three items per row: month, budget, actual. The lateral form handles this directly, because each tuple can have as many fields as needed, and the result has one row per department and month with both measures beside each other.

When unpivoting in SQL is the wrong step

The sheet is not loaded yet. If the data is still in Excel, Power Query's Unpivot Columns command does the same thing and remembers it for the next file (unpivoting columns into rows with Power Query). Do it before loading, and the database sees only the long form.

The columns differ in meaning. jan and phone_number do not belong in one column. Unpivot only columns that measure the same thing.

Pandas is the tool in use. DataFrame.melt does it in one line and is easier to maintain next to the rest of a script.

The wide form is the right shape. A report for people stays wide. Keep the long form as the stored data and pivot for display (pivoting rows into columns in PostgreSQL).

Documentation: PostgreSQL's page on LATERAL subqueries and DuckDB's page on UNPIVOT. To check a small result, use the CSV to JSON converter.