Unpivot Revenue and Units — Solution & ExplanationProblems / Unpivot Revenue and Units / EditorialApproach

One branch per quarter. Each SELECT names the quarter as a literal and pulls that quarter's *pair* of columns, aliased to the common revenue / units.

UNION ALL stacks the branches. No dedup semantics wanted — these are four disjoint slices. (Spark's DataFrame.union is already UNION ALL, by position.)

Order by product, quarter.

Key InsightWide formats smuggle data into metadata — the quarter lives in column names, invisible to WHERE, GROUP BY, or joins — and unpivoting is the act of moving it back into values. With multiple measure groups the operative concept is the unpivot grain: decide what one output row means (product-quarter) and make every branch deliver all measures for that grain at once. Unpivot measures separately and you pay for it immediately — NULL-padded rows or a self-join to reassemble what one pass of UNION ALL had for free.Hints

Unpivoting is SELECT-per-column stacked with UNION ALL; the quarter label enters as a literal ('Q1' AS quarter) because the wide table stores it only in column NAMES.

The two-measure twist: each branch selects BOTH q<N>_revenue and q<N>_units, aliased to the shared names revenue/units. Two separate single-measure unpivots would need a re-join on (product, quarter) to pair them back up.

Column names must align across UNION ALL branches — the first SELECT fixes them; later branches just need matching positions and types.

Sanity check the row count: products x 4 quarters. Eight rows per product means you unpivoted the measures separately.

Solve Unpivot Revenue and Units yourself →