Oracle Database – PIVOT and UNPIVOT

PIVOT rotates rows into columns. You hand it an aggregate, name the column whose values become the new column headings, and list which of those values you want. Oracle added it in 11.1, along with its inverse, UNPIVOT.

It is a reporting feature, and the reason to learn it properly is that the thing it replaces — a hand-written pile of CASE expressions — is what most codebases contain, and it is longer and noisier. The reason to be careful with it is that PIVOT performs a GROUP BY you never wrote, over every column you did not mention. That one fact explains almost every surprising result people get out of it.

The data

One narrow table of sales — three products, two regions, four quarters:

CREATE TABLE sales (
  id       NUMBER(6)         PRIMARY KEY,
  product  VARCHAR2(40 CHAR) NOT NULL,
  region   VARCHAR2(10 CHAR) NOT NULL,
  quarter  VARCHAR2(2 CHAR)  NOT NULL,
  amount   NUMBER(12,2)      NOT NULL
);

INSERT ALL
  INTO sales VALUES (1, 'Laptop',   'EAST', 'Q1', 1200)
  INTO sales VALUES (2, 'Laptop',   'EAST', 'Q2', 1500)
  INTO sales VALUES (3, 'Laptop',   'WEST', 'Q1',  900)
  INTO sales VALUES (4, 'Laptop',   'WEST', 'Q3', 1100)
  INTO sales VALUES (5, 'Monitor',  'EAST', 'Q1',  400)
  INTO sales VALUES (6, 'Monitor',  'WEST', 'Q2',  650)
  INTO sales VALUES (7, 'Monitor',  'WEST', 'Q4',  300)
  INTO sales VALUES (8, 'Keyboard', 'EAST', 'Q2',  120)
SELECT * FROM dual;

COMMIT;

Oracle rejects a multi-row VALUES list, hence INSERT ALL … SELECT * FROM dual — see the SELECT Essentials post. The ids are explicit for a reason worth knowing: a multi-table insert evaluates an identity column's sequence once for the whole statement, so GENERATED ALWAYS AS IDENTITY plus INSERT ALL hands every row the same id and dies on the primary key.

The first pivot

SELECT *
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4))
ORDER  BY product;

PRODUCT            Q1       Q2       Q3       Q4
---------- ---------- -------- -------- --------
Keyboard                   120
Laptop           2100     1500     1100
Monitor           400      650              300

Three clauses, each doing exactly one thing:

Clause	What it decides
`sum(amount)`	What goes in the cells. An aggregate is mandatory — a bare column gives `ORA-56902: non-aggregate expressions inside the PIVOT clause`.
`FOR quarter`	Which column's values become column headings.
`IN ('Q1' AS q1, …)`	Which of those values you want. Anything not listed is silently dropped.

That last point deserves repeating, because there is no error and no warning. Pivot the same data on IN ('Q1' AS q1, 'Q2' AS q2) and the Laptop row reads 2100 and 1500 — the 1100 of Q3 is simply gone, and any total you compute from the result is wrong. The IN list is a filter as much as it is a column list.

The GROUP BY you did not write

Here is the whole trick, and the whole trap. PIVOT groups by every column of its input that appears in neither the aggregate nor the FOR clause. Above, the input was an inline view of exactly three columns: amount went to the aggregate, quarter to the FOR, so the grouping key was product alone — which is the report we wanted.

Now pivot the table directly instead:

SELECT *
FROM   sales                                   -- not an inline view
PIVOT  (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4))
ORDER  BY product;

        ID PRODUCT    REGION         Q1       Q2       Q3       Q4
---------- ---------- ------ ---------- -------- -------- --------
         8 Keyboard   EAST                    120
         1 Laptop     EAST         1200
         2 Laptop     EAST                   1500
         3 Laptop     WEST          900
         4 Laptop     WEST                            1100
         5 Monitor    EAST          400
         6 Monitor    WEST                    650
         7 Monitor    WEST                                     300

Eight rows in, eight rows out, nothing aggregated at all. id and region came along, joined the grouping key, and id is unique — so every group holds exactly one row. No error, no warning: just a report that looks plausible on eight rows and is catastrophic on eight million.

Rule: always pivot an inline view selecting exactly the columns you want, and no others. Never pivot a table or a SELECT * directly. That also protects the query from the future — someone adds an audit column to sales next year and, with SELECT *, your report quietly changes shape.

The corollary is the useful half. To report by region as well, put region back in the inline view:

SELECT *
FROM   (SELECT product, region, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4))
ORDER  BY product, region;

PRODUCT    REGION         Q1       Q2       Q3       Q4
---------- ------ ---------- -------- -------- --------
Keyboard   EAST                   120
Laptop     EAST         1200     1500
Laptop     WEST          900              1100
Monitor    EAST          400
Monitor    WEST                   650                300

The grain of the report is controlled entirely from the inline view's select list. Nothing inside the PIVOT clause changed.

Naming the output columns

Alias every value in the IN list. Without an alias Oracle names the column after the literal — including its quotes — leaving you a column you can only reference as a quoted identifier:

-- The columns are literally named 'Q1' and 'Q2', apostrophes and all
SELECT product, "'Q1'" AS q1
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR quarter IN ('Q1', 'Q2'))
ORDER  BY product;

Numbers are worse: FOR year IN (2023, 2024) gives columns named 2023 and 2024, which are not legal unquoted identifiers either.

Three rules once you do alias:

The alias is an identifier, so it folds to upper case unless double-quoted. AS q1 produces a column named Q1; AS "q1" keeps the lower case and forces every reference to quote it.
With one aggregate the value alias is the column name. With more than one, Oracle concatenates <value alias>_<aggregate alias>.
That concatenation is subject to the identifier length limit — 128 bytes from 12.2, 30 before it — and Oracle truncates silently rather than raising an error. Two long aliases and you get a column named something you did not choose and cannot easily predict.

Several aggregates, several pivot columns

Both lists can hold more than one item. Multiple aggregates:

SELECT *
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  (sum(amount) AS amt, count(*) AS cnt
        FOR quarter IN ('Q1' AS q1, 'Q2' AS q2))
ORDER  BY product;

PRODUCT        Q1_AMT     Q1_CNT     Q2_AMT     Q2_CNT
---------- ---------- ---------- ---------- ----------
Keyboard                        0        120          1
Laptop           2100          2       1500          1
Monitor           400          1        650          1

Look at the two empty cells on the Keyboard row, which differ. Each pivot cell is its aggregate applied to the rows behind it, so an empty cell follows the aggregate's own rule for an empty set: sum gives NULL, count gives 0. A blank cell is not a zero unless the aggregate says so — use NVL when a report needs zeros:

SELECT product, nvl(q1, 0) AS q1, nvl(q2, 0) AS q2
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2))
ORDER  BY product;

Pivoting on a combination of columns means parenthesising both sides:

SELECT *
FROM   (SELECT product, region, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR (quarter, region) IN (('Q1','EAST') AS q1_east,
                                              ('Q1','WEST') AS q1_west,
                                              ('Q2','EAST') AS q2_east,
                                              ('Q2','WEST') AS q2_west))
ORDER  BY product;

PRODUCT       Q1_EAST    Q1_WEST    Q2_EAST    Q2_WEST
---------- ---------- ---------- ---------- ----------
Keyboard                                           120
Laptop           1200        900       1500
Monitor           400                              650

Which exposes the cost of the feature immediately: the IN list is the cross product of everything you want, written out by hand. Four quarters by two regions is eight entries; add a third region and you edit the query.

The IN list must be constant

This is the hard limitation, and the one that sends people looking for workarounds. The values have to be known at parse time, and Oracle has a distinct error for each way of getting that wrong:

-- A subquery: rejected by the parser itself
… PIVOT (sum(amount) FOR quarter IN (SELECT DISTINCT quarter FROM sales));
-- ORA-00936: missing expression

-- A bind variable: recognised, and specifically refused
… PIVOT (sum(amount) FOR quarter IN (:q AS q1));
-- ORA-56900: The bind variable is not supported inside a pivot or unpivot operation.

-- A column reference
… PIVOT (sum(amount) FOR quarter IN (product AS q1));
-- ORA-56901: non-constant expression in pivot or unpivot values clause

Constant expressions are fine, though — the restriction is on values Oracle cannot fold before the plan exists, not on literals specifically. IN (upper('q1') AS q1) compiles and works.

The reason is structural rather than an Oracle quirk: a result set's column list is fixed when the cursor is parsed, and a query whose columns depend on its own data cannot be parsed. Every other database with a pivot operator has the same restriction. Three ways out, in the order they are usually the right answer:

1. Generate the SQL. Query the distinct values, build the IN list as a string, execute it. Honest and debuggable, and the usual answer:

SELECT listagg('''' || quarter || ''' AS ' || quarter, ', ')
         WITHIN GROUP (ORDER BY quarter) AS in_list
FROM   (SELECT DISTINCT quarter FROM sales);

-- 'Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4

Paste that into the query, or concatenate it in PL/SQL and OPEN … FOR a ref cursor. Bind the filter values; only the column list should ever be concatenated, or you have written an SQL injection. If the pivot values come from user input rather than from a controlled column, check them against the real distinct values before they reach the string.

2. PIVOT XML with ANY. Oracle's own answer to the dynamic case, and the one place a subquery in the IN list is legal. It sidesteps the problem by returning a single XMLType column, so the shape is fixed after all:

SELECT *
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  XML (sum(amount) FOR quarter IN (ANY));

PRODUCT    QUARTER_XML
---------- --------------------------------------------------------------------------
Keyboard   <PivotSet><item><column name = "QUARTER">Q2</column><column name = "SUM(AM
           OUNT)">120</column></item></PivotSet>
Laptop     <PivotSet><item><column name = "QUARTER">Q1</column><column name = "SUM(AM
           OUNT)">2100</column><item>…

Now the client has to parse XML, which is usually a worse problem than the one you started with. It is worth knowing it exists and rarely worth using.

3. Do not pivot in SQL at all. Return the narrow rows and let the report layer — the spreadsheet, the BI tool, the grid component — do the rotation. Pivoting is a presentation concern, and those tools do it dynamically without being asked twice.

UNPIVOT: columns back into rows

The inverse, and in practice the more useful of the two, because it is how you rescue a spreadsheet-shaped table into something you can actually query. Take the pivoted output as a table:

CREATE TABLE quarterly_sales AS
SELECT *
FROM   (SELECT product, quarter, amount FROM sales)
PIVOT  (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4));

SELECT * FROM quarterly_sales
UNPIVOT (amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4'))
ORDER   BY product, quarter;

PRODUCT    QU     AMOUNT
---------- -- ----------
Keyboard   Q2        120
Laptop     Q1       2100
Laptop     Q2       1500
Laptop     Q3       1100
Monitor    Q1        400
Monitor    Q2        650
Monitor    Q4        300

The clause reads in the opposite direction to PIVOT, and keeping that straight is most of the battle: amount is the new value column, quarter is the new label column, and the IN list names the existing columns to fold in, each aliased to the label it should carry. Drop the aliases and the labels are the column names, upper-cased.

Three things to know:

NULLs are dropped by default. Seven rows above, not twelve — Keyboard has no Q1, Q3 or Q4. UNPIVOT INCLUDE NULLS gives the full 12-row grid. This is the opposite of the default everywhere else in SQL, so it catches people in both directions.
The folded columns must share a datatype. Mix a NUMBER column with a VARCHAR2 one and you get ORA-01790: expression must have same datatype as corresponding expression. Cast them in an inline view first.
No aggregate is involved. UNPIVOT is a row generator, not a grouping operation, so none of the GROUP BY subtleties above apply to it. It is the safe half of the pair.

Parenthesise to fold several columns into one output row, which is how you unpivot a table with paired columns — the multi-aggregate pivot from earlier, run backwards:

-- quarterly_detail is (product, q1_amt, q1_cnt, q2_amt, q2_cnt)
SELECT * FROM quarterly_detail
UNPIVOT ((amt, cnt) FOR quarter IN ((q1_amt, q1_cnt) AS 'Q1',
                                    (q2_amt, q2_cnt) AS 'Q2'))
ORDER   BY product, quarter;

PRODUCT    QU        AMT        CNT
---------- -- ---------- ----------
Keyboard   Q1                     0
Keyboard   Q2        120          1
Laptop     Q1       2100          2
Laptop     Q2       1500          1
Monitor    Q1        400          1
Monitor    Q2        650          1

Keyboard's Q1 row survives here, despite a null amt, because the pair's cnt is 0 and not null — EXCLUDE NULLS drops a row only when every folded column in it is null.

The CASE alternative, and when it is better

Every PIVOT is a GROUP BY with conditional aggregates. This is what the feature replaced, it works on any version and any database, and it is what PIVOT becomes internally:

SELECT product,
       sum(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
       sum(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
       sum(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
       sum(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM   sales
GROUP  BY product
ORDER  BY product;

Same result, same work — a full scan and a hash group by, in both cases. Written out this way the implicit grouping stops being implicit, which is exactly why the manual form is harder to get wrong, and why it is worth knowing even if you prefer PIVOT's brevity.

Prefer	When
`PIVOT`	Many columns under the same aggregate — twelve months across stays readable where twelve `CASE` expressions do not.
`CASE`	A different aggregate or condition per column: `sum` here, `max` there, a condition on a second column. `PIVOT` applies the same aggregate list to every value.
`CASE`	The query must also run on 10g, or on another database.
`CASE`	You want the grouping key visible in the query text, because someone will edit this later.

What the plan looks like

EXPLAIN PLAN FOR
SELECT * FROM (SELECT product, quarter, amount FROM sales)
PIVOT (sum(amount) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4));

SELECT * FROM table(dbms_xplan.display(format => 'BASIC'));

-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  HASH GROUP BY PIVOT|       |
|   2 |   TABLE ACCESS FULL | SALES |
-------------------------------------

One line tells you everything: HASH GROUP BY PIVOT. There is no pivot algorithm — it is an ordinary group-by whose aggregates happen to be conditional. So the performance question is never "is PIVOT slow"; it is the same question as for any aggregate — how much of the table must be read, and can an index cover it.

Two practical consequences:

Filter inside the inline view. A WHERE on the pivot's output runs after aggregation; the same predicate in the inline view eliminates rows before it, and can use an index.
The source is read once. That is PIVOT's real advantage over the shape it usually replaces in older code — four correlated scalar subqueries, or four self-joins, one per quarter.

The checklist

Pivot an inline view listing exactly the columns you want, never a table.
Everything not in the aggregate or the FOR clause becomes the grouping key.
Alias every value in the IN list, and keep the aliases short.
Values missing from the IN list are dropped without a word.
An empty cell is whatever the aggregate returns for no rows: NULL from sum, 0 from count.
The IN list must be constant. Generate the SQL, or pivot in the report layer.
UNPIVOT excludes NULLs by default and needs one datatype across the folded columns.

PL/SQL — Oracle's procedural language, and the reason a lot of business logic in enterprise systems lives inside the database rather than in front of it. It is also where a dynamically generated pivot ends up living.