How to transform single database row with multiple columns into separate rows for each column

Data and Databases
1

I’m working with a database table called SALES_DATA where each record contains quarterly revenue figures. Here’s what my current structure looks like:

RecordID Location Category Q1 Q2 Q3
100 Store_X 5 1250 890 675
200 Store_Y 7 720 1040 930

I need to restructure this data so each quarterly value becomes its own row:

RecordID Location Category Period Revenue
100 Store_X 5 Q1 1250
100 Store_X 5 Q2 890
100 Store_X 5 Q3 675
200 Store_Y 7 Q1 720
200 Store_Y 7 Q2 1040
200 Store_Y 7 Q3 930

Right now I’m using this approach:

SELECT RecordID, Location, 'Q1' AS Period, Q1 AS Revenue FROM SALES_DATA
UNION ALL
SELECT RecordID, Location, 'Q2' AS Period, Q2 AS Revenue FROM SALES_DATA
UNION ALL
SELECT RecordID, Location, 'Q3' AS Period, Q3 AS Revenue FROM SALES_DATA

This works but feels clunky. I deal with tables that have different numbers of columns like annual data with 12 months or regional data with many location columns. There has to be a more flexible solution that doesn’t require writing separate UNION statements for each column. What’s the best way to handle this kind of pivot operation?

2

If your db supports it, try cross apply with a table value constructor. Great for dynamic colums - no hardcoding needed. Like cross apply (values ('Q1', Q1), ('Q2', Q2), ('Q3', Q3)) as unpvt(period, revenue). Way easier when your column structure keeps changing.

3

UNPIVOT is exactly what you need here. It’s way cleaner than stacking multiple UNION statements. Your query becomes:

SELECT RecordID, Location, Category, Period, Revenue
FROM SALES_DATA
UNPIVOT (Revenue FOR Period IN (Q1, Q2, Q3))

I’ve used this tons with financial reports that keep changing structure. The real win shows when you’ve got dozens of columns to unpivot. For monthly data, just expand the IN clause with all twelve months - no extra UNION statements needed. Performance is much better too since it only scans the base table once instead of multiple times. Most modern databases support UNPIVOT, though syntax varies between vendors.

4

Oh cool! I’ve been dealing with the same kind of transformations recently. What database are you using? Some have handy features like VALUES clauses or cross joins that might work better for your situation. And what about null values in those quarterly columns - keep the rows or drop them?