UNPIVOT

UNPIVOT is a SQL operation, introduced in Oracle 11g, that rotates data from columns into rows.

Note that UNPIVOT is not the reverse of a PIVOT operation as it cannot undo aggregations made by PIVOT.

Example

Create a test table with sample data:

CREATE TABLE saleshist (product VARCHAR2(30), q1 NUMBER, q2 NUMBER, q3 NUMBER, q4 NUMBER);
INSERT INTO saleshist VALUES ('Oracle EE', 100, 123, 130, 128);
INSERT INTO saleshist VALUES ('Partitioning', 100, 123, 130, 128);

Perform an unpivot on it:

SELECT * FROM saleshist
  UNPIVOT INCLUDE NULLS (quantity_sold FOR quarter IN (Q1, Q2, Q3, Q4));
PRODUCT                        QU QUANTITY_SOLD
------------------------------ -- -------------
Oracle EE                      Q1           100
Oracle EE                      Q2           123
Oracle EE                      Q3           130
Oracle EE                      Q4           128
Partitioning                   Q1           100
Partitioning                   Q2           123
Partitioning                   Q3           130
Partitioning                   Q4           128

Also see

• PIVOT