How to Use PIVOT in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
PIVOT turns row values into columns by applying an aggregate per value in an IN list. The list must be literal; dynamic PIVOT needs hand-built SQL.
SELECT *
FROM (
SELECT product_category, EXTRACT(MONTH FROM order_date) AS order_month, order_total
FROM orders
)
PIVOT (
SUM(order_total)
FOR order_month IN (1 AS jan, 2 AS feb, 3 AS mar)
);Multiple aggregates
PIVOT can compute more than one aggregate per bucket; Oracle names each output column by combining the aggregate alias and the IN-list alias.
SELECT *
FROM (
SELECT department_id, job_id, salary
FROM employees
)
PIVOT (
SUM(salary) AS total, COUNT(*) AS cnt
FOR job_id IN ('IT_PROG' AS it_prog, 'SA_REP' AS sales)
);The IN list has to be known at parse time; you cannot feed it a subquery or a bind variable of values. For a variable set of pivot columns, either generate the PIVOT statement dynamically with PL/SQL, or reach for XML PIVOT which returns the result as XML instead of fixed columns. UNPIVOT does the reverse, turning a fixed set of columns back into rows.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free