In this example, the sales and cogs columns are unpivoted.
SELECT *
FROM star1p UNPIVOT ((sales,cogs) FOR yr_qtr
IN ((Q101Sales, Q101Cogs) AS ‘Q101’,
(Q201Sales, Q201Cogs) AS ‘Q201’,
(Q301Sales, Q301Cogs) AS ‘Q301’)) Tmp;
The output for the unpivoted table:
country state yr_qtr sales cogs ------- ----- ------ ----------- ----------- Canada ON Q201 10 0 Canada ON Q301 10 0 USA NY Q101 45 25 USA CA Q101 30 15 USA CA Q201 50 20
Note that a pivot combined with a matching unpivot may introduce rows with NULL values. It is possible to unpivot just the ‘yr’ column.