|
|
|
UNPIVOT
Declaration
SELECT [ <non-pivoted column> [ AS <column name> ], ]
…
[ <first pivoted column [AS <column name>] ,
[ <second pivoted column [AS <column name>] ,]
…
[ <last pivoted column [AS <column name>] ] ]
FROM
(<SELECT query that produces the data>)
AS <alias for query that produces the data>
PIVOT
(
<aggregation function> ( <aggregated column> )
FOR <column that contains values that will be column headers>
IN ( <first pivoted column>
, <second pivoted column>
, … <last pivoted column>)
) AS <pivot table alias>
[ <optional ORDER BY clause> ]
Description
UNPIVOT operates on a table-valued expression, like the reverse of PIVOT, rotating columns into column values. UNPIVOT is not an exact reverse of PIVOT as NULL values in UNPIVOT's input will not show in the output and rows have been merged.
It selects data from a table-valued expression, transposing columns to rows.
Example
select * from product_sales;
PRODUCT_NAME STORE_LOCATI NUM_SALES
------------ ------------ ---------
Chair North 55
Desk Central 120
Couch Central 78
Chair South 23
Chair South 10
Chair North 98
Desk West 61
Couch North 180
Chair South 14
Desk North 45
Chair North 87
Chair Central 34
Desk South 42
Couch West 58
Couch Central 27
Chair South 91
Chair West 82
Chair North 37
Desk North 68
Couch Central 54
Chair South 81
Desk North 25
Chair North 46
Chair Central 121
Desk South 85
Couch North 43
Desk West 10
Chair North 5
Chair Central 16
Desk South 9
Couch West 22
Couch Central 59
Chair South 76
Chair West 48
Chair North 19
Desk North 3
Couch West 63
Chair South 81
Desk North 85
Chair North 90
Chair Central 47
Desk West 63
Couch North 28
43 records selected
select store_location, product_name, num_sales from pivot_table unpivot ( num_sales for
store_location in (central, south, west, north)) as upvt;
STORE_LOCATION PRODUCT_NAME NUM_SALES
-------------- ------------ ---------
central Couch 218
west Couch 143
north Couch 251
central Chair 218
south Chair 376
west Chair 130
north Chair 437
central Desk 120
south Desk 136
west Desk 134
north Desk 226
11 records selected
See Also
