Unpivoting Columns Into Rows With CROSS APPLY and VALUES

A report stores one month per column, but the chart needs one month per row. Unpivoting columns with CROSS APPLY and VALUES makes that change explicit. You can keep missing values and related measures together instead of letting the operator choose for you.

Define the Output Grain First

A wide table can be convenient for entry and awkward for reporting. Unpivoting columns creates one row for each source identity and listed column. State that grain before writing SQL.

A missing month, a zero amount, and an absent source row are different conditions. Decide which of them the consumer needs to see. The conversion should preserve those distinctions unless the business rule removes them deliberately.

I check whether the wide table is an import layout or the permanent application schema. For ongoing monthly data, a normalized child table is easier to extend. A transformation is still useful at the boundary.

The examples below use a local temporary table and invented amounts. They demonstrate the shape without making claims about observed rows or sales in a live report.

Unpivoting Columns Into Rows One Tuple at a Time

CROSS APPLY evaluates the VALUES constructor for each source row. Each tuple supplies the label and corresponding amount. That creates an explicit connection between source column and output label.

The source identity remains available outside the constructor. Keep it in the result so rows from several departments or products don't become indistinguishable after the table changes shape.

The three tuples are declarations of the sample layout. Adding another source column requires another tuple. That is visible maintenance, rather than hidden dynamic SQL.

Inspect the result with an ORDER BY because SQL doesn't promise tuple presentation order in the final output. A period sequence is more useful than alphabetical labels when the chart needs a chronological axis.

CREATE TABLE #MonthlyWide
(
    ProductId int NOT NULL,
    JanAmount decimal(19,4) NULL, FebAmount decimal(19,4) NULL, MarAmount decimal(19,4) NULL,
    JanUnits int NULL, FebUnits int NULL, MarUnits int NULL
);
INSERT #MonthlyWide VALUES (1,10,NULL,30,2,NULL,6),(2,0,15,20,0,3,4);
SELECT w.ProductId, v.MonthNumber, v.MonthLabel, v.Amount
FROM #MonthlyWide AS w
CROSS APPLY (VALUES (1,N'Jan',w.JanAmount),
                    (2,N'Feb',w.FebAmount),
                    (3,N'Mar',w.MarAmount)) AS v(MonthNumber,MonthLabel,Amount)
ORDER BY w.ProductId,v.MonthNumber;

Preserve NULL Until You Choose Otherwise

VALUES retains a tuple whose amount is NULL. That lets a report display a missing period or flag an incomplete load. Add WHERE v.Amount IS NOT NULL when dropping that row matches the requirement.

Don't convert NULL to zero just to make the chart look continuous. Zero is a measured or declared value. A missing amount can mean the source hasn't arrived.

I inspect the no-data case before comparing query performance. A fast transformation that loses missing periods changes the report's meaning. Keep the decision near the final predicate so another reader can see it.

If one measure is missing while another is present, define whether the row remains. That becomes especially important when unpivoting several paired measures in the same output.

Compare the UNPIVOT Operator

UNPIVOT offers a concise operator for a compatible set of columns. Its output labels come from the listed column names. It drops NULL measure values.

That behavior is useful for some reports and wrong for others. Compare the sample output with the VALUES form. VALUES returns six rows, while UNPIVOT returns five, because product 1 has no February amount. The difference is semantics, not merely the style of SQL used.

UNPIVOT also requires compatible source types. A VALUES constructor resolves a common type for each tuple position using normal precedence rules. Explicit casts help when source columns differ.

Don't allow a numeric column to force conversion of descriptive text because the tuple positions were arranged carelessly. Name the output columns clearly and keep the same meaning in each position.

SELECT ProductId, SourceColumn, Amount
FROM (SELECT ProductId,JanAmount,FebAmount,MarAmount FROM #MonthlyWide) AS w
UNPIVOT (Amount FOR SourceColumn IN (JanAmount,FebAmount,MarAmount)) AS u;

Keep Paired Measures Aligned When Unpivoting Columns

Unpivot amount and units together in the same tuple. Two independent transformations joined only by ProductId create combinations across months. That multiplies rows and pairs January's amount with February's units.

A shared month label is part of the key if you transform separately. A single VALUES list avoids that second alignment problem and makes each pair visible in one place.

The next query creates one row per product and month with both measures. The explicit MonthNumber supports ordering and joins to a calendar dimension. Don't use a short month name as the only time identity when several years appear.

Include the year or a proper date in a real load. January is a useful label, but it isn't a globally unique period identifier.

SELECT w.ProductId,v.MonthNumber,v.Amount,v.Units
FROM #MonthlyWide AS w
CROSS APPLY (VALUES (1,w.JanAmount,w.JanUnits),
                    (2,w.FebAmount,w.FebUnits),
                    (3,w.MarAmount,w.MarUnits)) AS v(MonthNumber,Amount,Units)
ORDER BY w.ProductId,v.MonthNumber;

Retain the Label as Data

Carry a source-column label when support needs to trace the output back to an import field. A reporting label and a source-field name serve different purposes. Store both when the audit path requires them.

Avoid parsing labels back into logic later. The tuple already defines the source mapping. Keep it explicit in the row instead of forcing another string transformation.

Which source column produced the amount a user is questioning? A clear label answers that without another spreadsheet exercise. Also retain a load identity for imported data.

It distinguishes separate files containing the same product and month. The transformation is straightforward. The confusion usually arrives when two sources are combined without keeping enough identity to explain why their rows coexist.

Filter Before Expanding Where Appropriate

Apply product or source-load filters before generating the extra rows when that preserves the requirement. Inspect the plan and reads for representative inputs. CROSS APPLY isn't automatically slow, and UNPIVOT isn't automatically faster.

The optimizer works with the full expression. Choose the form that states the needed null handling and measure alignment, then measure the request your system actually runs.

For a recurring load, insert the transformed rows into a normalized table with the correct unique key. Validate the period and required measures before insertion. Keep malformed or incomplete input available for review.

Unpivoting columns fixes the data's shape. It doesn't certify that every amount is valid or that every expected month was delivered by the source.

Make Unpivoting Columns Easy to Review

Keep the VALUES list aligned and label each tuple consistently. Test NULL, zero, duplicate source identities, and a new month column. Review paired fields together when the import layout changes.

A transformation should fail visibly or be updated deliberately when the schema grows. Silent omission of the new month is a correctness problem even if every old tuple still works.

Save an expected output for the small fixture and compare the real load by its intended key. Keep totals consistent across the reshape when the business rule retains the same values. Rows should multiply only by the declared periods.

If the counts grow unexpectedly, look for independent unpivots crossing each other. A monthly report doesn't need to invent extra months to stay interesting.

Related reading on this blog: CROSS APPLY and OUTER APPLY in Everyday Queries and Exploring PIVOT and UNPIVOT.

A reshape is not a change in meaning, it is the same measures under an explicit row identity.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.