Reports often need a cross-tab: flight statuses down the side, months across the top, and a count in each cell. In the table, each of those values is a row. The PIVOT clause turns the values of one column into separate columns, each holding an aggregate, so the cross-tab comes straight out of one query.
This guide covers the PIVOT syntax, multiple aggregates, how PIVOT decides its grouping, rounding the results, and the conditional aggregation it replaces.
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 (source_query)
pivot (aggregate(expr) [as alias] [, aggregate(expr) [as alias] ...]
for column in (value [as alias] [, value [as alias] ...]))PIVOT groups the rows by every source column it does not mention, and makes one column for each value listed in IN, holding the aggregate for that value. The IN values must be constants.
A Cross-Tab by Status and Month
The source query returns each flight's month and status. PIVOT counts the flights for each status, with one column per month.
Example:
select *
from (select to_char(sys_extract_utc(scheduled_departure), 'MM') as month, status
from flights)
pivot (count(*) for month in ('01' as jan, '02' as feb, '03' as mar));Output:
STATUS JAN FEB MAR ____________ ______ ______ ______ ARRIVED 963 871 429 CANCELLED 18 17 15 SCHEDULED 0 0 537
Status is the only source column PIVOT does not mention, so it becomes the grouping column, one row per status. A combination with no rows, such as scheduled flights in January, shows 0 for COUNT and would show NULL for SUM or AVG.
Several Aggregates at Once
PIVOT can compute several aggregates. Each one gets its own set of columns, named from the value's alias and the aggregate's alias joined by an underscore: BUSINESS_TICKETS, BUSINESS_FARES, and so on.
Example:
select *
from (select b.status, t.cabin, t.fare
from bookings b join tickets t on t.booking_id = b.booking_id)
pivot (count(*) as tickets, sum(fare) as fares
for cabin in ('BUSINESS' as business, 'ECONOMY' as economy));Output:
STATUS BUSINESS_TICKETS BUSINESS_FARES ECONOMY_TICKETS ECONOMY_FARES ____________ ___________________ _________________ __________________ ________________ COMPLETED 180 410381.36 563 351704.41 CONFIRMED 72 157719.29 201 137509.86 CANCELLED 18 50245.14 35 23218.59
Every Unmentioned Column Is a Grouping Column
The most common PIVOT surprise is an extra column in the source query. PIVOT groups by it too, so the result gets more rows than expected. Here the aircraft type is in the source, and the cancelled flights are split by type.
Example:
-- an extra column in the source becomes a grouping column: one row per aircraft type
select *
from (select to_char(sys_extract_utc(f.scheduled_departure), 'MM') as month,
f.status, a.type_code
from flights f join aircraft a on a.tail_number = f.tail_number
where f.status = 'CANCELLED')
pivot (count(*) for month in ('01' as jan, '02' as feb, '03' as mar))
order by type_code;Output:
STATUS TYPE_CODE JAN FEB MAR ____________ ____________ ______ ______ ______ CANCELLED A20N 4 2 2 CANCELLED A21N 1 1 3 CANCELLED B77W 4 5 7 CANCELLED B789 9 9 3
That is useful when you want it, as here. When you do not, select only the grouping column, the FOR column, and the aggregated column in the source query, never a primary key, which would make every row its own group.
Round in an Outer Query
The aggregate must be the outermost expression inside PIVOT. Wrapping it in another function fails:
Example:
select *
from (select b.status, t.cabin, t.fare
from bookings b join tickets t on t.booking_id = b.booking_id)
pivot (round(sum(fare)) as fares for cabin in ('BUSINESS' as business, 'ECONOMY' as economy));Output:
Error starting at line : 1
In command -
select *
from (select b.status, t.cabin, t.fare
from bookings b join tickets t on t.booking_id = b.booking_id)
pivot (round(sum(fare)) as fares for cabin in ('BUSINESS' as business, 'ECONOMY' as economy))
Error at Command Line : 4 Column : 9
Error report -
SQL Error: ORA-56902: non-aggregate expressions inside the PIVOT clauseSelect the pivoted columns by name in the outer query and round them there.
Example:
select status, round(business_fares) as business_fares, round(economy_fares) as economy_fares
from (select b.status, t.cabin, t.fare
from bookings b join tickets t on t.booking_id = b.booking_id)
pivot (sum(fare) as fares for cabin in ('BUSINESS' as business, 'ECONOMY' as economy))
order by status;Output:
STATUS BUSINESS_FARES ECONOMY_FARES ____________ _________________ ________________ CANCELLED 50245 23219 COMPLETED 410381 351704 CONFIRMED 157719 137510
The Conditional Aggregation Alternative
PIVOT is shorthand for conditional aggregation, an aggregate over a CASE expression for each column. This gives the same cross-tab as the first example.
Example:
-- the same cross-tab with conditional aggregation
select status,
count(case when month = '01' then 1 end) as jan,
count(case when month = '02' then 1 end) as feb,
count(case when month = '03' then 1 end) as mar
from (select to_char(sys_extract_utc(scheduled_departure), 'MM') as month, status
from flights)
group by status;Output:
STATUS JAN FEB MAR ____________ ______ ______ ______ ARRIVED 963 871 429 CANCELLED 18 17 15 SCHEDULED 0 0 537
Conditional aggregation is longer but more flexible: each column can have its own condition and aggregate, and the grouping is written out in GROUP BY.
Things to Know
- The IN list must hold constants. For columns that depend on the data, generate the query text dynamically, or use PIVOT XML, which accepts a subquery or ANY but returns the result as XML.
- Without aliases in the IN list, the column names are the values themselves, quoted, such as '01'; give each value an alias.
- WHERE and ORDER BY of the outer query see the pivoted columns, so you can filter or sort on them.
Related Guides
- How to Summarize Every Combination with CUBE in Oracle SQL
- How to Create a Pivot Table in Python Using Pandas
Conclusion
PIVOT turns the values of one column into columns of aggregates, grouping by every source column it does not mention. Keep the source query to the columns you need, alias the IN values and the aggregates to name the result columns, round in an outer query, and fall back on conditional aggregation when each column needs its own logic.
