Pivot and Unpivot

The PIVOT clause allows you to create a pivot table with column names and value columns by rotating the vertical pivot column into horizontal columns.

The general syntax for the PIVOT clause is:

SELECT * FROM table PIVOT 
(
  [name_col1, name_col2,] aggregate_function(col) FOR pivot_col IN (value1, value2,...)
)

The UNPIVOT clause allows you to rotate a table by transforming pivot columns into rows.

The general syntax for the UNPIVOT clause is:

SELECT * FROM table UNPIVOT 
(
  [name_col1, name_col2,] value_col FOR pivot_col IN (value1, value2,...)
)

Case Examples:

The following command generate a pivot table with total quantity in each channel.

SELECT * FROM sales PIVOT (Customer.gender AS gender, SUM(qty) FOR channel_name IN (*))

+--------+-------+-------+-------+--------+--------+---------+---------+
| gender | 7-11  | CVS   | am/pm | amazon | costco | unknown | walmart |
+--------+-------+-------+-------+--------+--------+---------+---------+
| Female | 53490 | 27900 | 82570 |  69872 |  14406 |    2823 |   28160 |
| Male   | 51227 | 27729 | 81097 |  67771 |  12973 |    2494 |   27010 |
+--------+-------+-------+-------+--------+--------+---------+---------+

To unpviot the above pivot table, use the command below:

SELECT * FROM (
   SELECT * FROM sales PIVOT (Customer.gender AS gender, SUM(qty) FOR channel_name IN (*))
) UNPIVOT (gender, total_qty FOR channel_name IN(*));

+--------+--------------+-----------+
| gender | channel_name | total_qty |
+--------+--------------+-----------+
| Female | 7-11         |     53490 |
| Female | CVS          |     27900 |
| Female | am/pm        |     82570 |
| Female | amazon       |     69872 |
| Female | costco       |     14406 |
| Female | unknown      |      2823 |
| Female | walmart      |     28160 |
| Male   | 7-11         |     51227 |
| Male   | CVS          |     27729 |
| Male   | am/pm        |     81097 |
| Male   | amazon       |     67771 |
| Male   | costco       |     12973 |
| Male   | unknown      |      2494 |
| Male   | walmart      |     27010 |
+--------+--------------+-----------+