This example showcases using UNPIVOT to capture elaborate data of a base table (star1p, in this case). The data is spread over many columns into a compact table with an optimal number of columns and no data loss.
SELECT * FROM star1p UNPIVOT (measure_value FOR yr_qtr_measure IN (Q101Sales, Q201Sales, Q301Sales,Q101Cogs, Q201Cogs, Q301Cogs)) Tmp; country state yr_qtr_measure measure_value ------- ----- -------------- ------------- Canada BC Q301Cogs 0 Canada BC Q301Sales 10 Canada ON Q201Cogs 0 Canada ON Q201Sales 10 USA CA Q101Cogs 15 USA CA Q101Sales 30 USA CA Q201Cogs 20 USA CA Q201Sales 50 USA NY Q101Cogs 25 USA NY Q101Sales 45