Some tables store related values side by side in columns: business seats and economy seats, a salary and a commission, one column per quarter. That layout is convenient to read but awkward to query, because a question about "any cabin" has to name every column. The UNPIVOT clause turns those columns into rows, one row per column, with the column's name in one column and its value in another.
Code for This Guide
The main examples are in the examples/pivot-model folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
select ...
from table_or_subquery
unpivot [{include | exclude} nulls]
(value_column for name_column in (column [as 'name'] [, ...]))value_column and name_column are new columns that UNPIVOT creates. Each listed column becomes a row: its alias goes into name_column and its value into value_column. Columns not listed are repeated on every row.
One Row per Cabin
AIRCRAFT_TYPES has a seats_business and a seats_economy column. UNPIVOT turns them into one row per cabin, with the cabin's name and seat count.
Example:
select type_code, cabin, seats from aircraft_types unpivot (seats for cabin in (seats_business as 'BUSINESS', seats_economy as 'ECONOMY')) where type_code like 'B%' order by type_code, cabin;
Output:
TYPE_CODE CABIN SEATS ____________ ___________ ________ B77W BUSINESS 42 B77W ECONOMY 312 B789 BUSINESS 30 B789 ECONOMY 260
The outer WHERE and ORDER BY work on the unpivoted result, so they can use the new cabin and seats columns.
What It Replaces
Without UNPIVOT, the same rows need one query per column, joined with UNION ALL, each reading the table again.
Example:
-- the same rows as UNPIVOT, written with UNION ALL select type_code, 'BUSINESS' as cabin, seats_business as seats from aircraft_types where type_code like 'B%' union all select type_code, 'ECONOMY', seats_economy from aircraft_types where type_code like 'B%' order by type_code, cabin;
Output:
TYPE_CODE CABIN SEATS ____________ ___________ ________ B77W BUSINESS 42 B77W ECONOMY 312 B789 BUSINESS 30 B789 ECONOMY 260
Keep NULL Values with INCLUDE NULLS
By default, UNPIVOT leaves out rows whose value is NULL (EXCLUDE NULLS). Only sales staff have a commission, so unpivoting salary and commission would drop the commission row of everyone else. INCLUDE NULLS keeps it.
Example:
select employee_id, measure, value
from (select employee_id, salary, commission_pct * 100 as commission from employees
where employee_id in (150, 154))
unpivot include nulls (value for measure in (salary as 'SALARY',
commission as 'COMMISSION %'))
order by employee_id, measure;Output:
EMPLOYEE_ID MEASURE VALUE
______________ _______________ ________
150 COMMISSION %
150 SALARY 21000
154 COMMISSION % 10
154 SALARY 8200Employee 150 has no commission, and the row is still there with an empty value. All the listed columns must have compatible types; here the commission is computed as a number to match the salary.
Unpivot Several Columns at Once
UNPIVOT can produce several value columns together by listing column groups in parentheses. This query returns the seats and the percentage of seats for each cabin of the Airbus types.
Example:
-- two value columns at once: seats and a share of the total seats per cabin
select type_code, cabin, seats, pct
from (select type_code, seats_business, seats_economy,
round(100 * seats_business / (seats_business + seats_economy)) as pct_business,
round(100 * seats_economy / (seats_business + seats_economy)) as pct_economy
from aircraft_types)
unpivot ((seats, pct) for cabin in ((seats_business, pct_business) as 'BUSINESS',
(seats_economy, pct_economy) as 'ECONOMY'))
where type_code like 'A%'
order by type_code, cabin;Output:
TYPE_CODE CABIN SEATS PCT ____________ ___________ ________ ______ A20N BUSINESS 12 7 A20N ECONOMY 150 93 A21N BUSINESS 16 8 A21N ECONOMY 180 92 A359 BUSINESS 42 13 A359 ECONOMY 283 87 6 rows selected.
Each group in IN must have as many columns as the list of value columns, (seats, pct) here.
Related Guides
Conclusion
UNPIVOT turns a set of columns into rows, putting each column's alias in a name column and its value in a value column. It replaces chains of UNION ALL queries, drops NULL values unless you add INCLUDE NULLS, and can unpivot groups of columns into several value columns at once.
