|
|
|
PIVOT
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
PIVOT operates on a table-valued expression, transforming unique values from one column in the expression into multiple columns in the result set. PIVOT can also aggregate other columns from the table-valued expression as additional columns in the result set.
It selects data from a table-valued expression, transposing rows to columns. The unique values from one of the columns provides the columns for the resulting result-set.
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 product_name,
Central, North, South, West
FROM product_sales
PIVOT
(
sum(num_sales)
FOR store_location IN (North, Central, South, West)
) AS pivot_table;
PRODUCT_NAME CENTRAL NORTH SOUTH WEST
------------ ------- ----- ----- ----
Couch 218 251 143
Chair 218 437 376 130
Desk 120 226 136 134
3 records selected
See Also
