Pivot and Unpivot

The PIVOT and UNPIVOT operators (introduced in Oracle 11g) reshape data between "long" and "wide" formats — converting rows into columns and back.

This is the most common request in any reporting or analytics task: turn category-level rows into one row per entity with one column per category. Without PIVOT, you would write tedious CASE expressions for every category; with PIVOT, a one-liner.

When to Use PIVOT

Suppose you have salary data by department and year:

salary_history:

A report consumer wants to see one row per department with columns for each year:

That transformation is a pivot. The reverse — turning column-based data back into row form — is an unpivot.

PIVOT Anatomy

SELECT *
FROM   salary_history
PIVOT (
  SUM(total_salary)
  FOR year IN (2022, 2023, 2024)
);

Result:

Three things define every PIVOT:

1. The aggregate function — SUM(total_salary). PIVOT must aggregate because multiple rows can map to one cell.
2. The pivot column — FOR year. Which column's values become new column headers.
3. The pivot values — IN (2022, 2023, 2024). Which discrete values to turn into columns.

Why You Must Aggregate

PIVOT works at the intersection of (output row, output column). Multiple input rows can share an intersection, so PIVOT requires an aggregate to collapse them. Even when each intersection has exactly one input row, you still need an aggregate — SUM, MAX, MIN, etc. all return that single value unchanged.

SELECT *
FROM   salary_history
PIVOT (
  MAX(total_salary)    -- works the same; one row per intersection
  FOR year IN (2022, 2023, 2024)
);

What Happens to "Other" Columns?

Every column from the source query that is not named in either the aggregate or the FOR clause becomes a grouping column in the output.

Compare these two queries:

-- 1. department_id is the only "other" column → group by department
SELECT * FROM salary_history
PIVOT (SUM(total_salary) FOR year IN (2022, 2023, 2024));
-- 2 rows: one per department

-- 2. Add region to the source → region also becomes a grouping column
SELECT * FROM (
  SELECT department_id, region, year, total_salary FROM salary_history
)
PIVOT (SUM(total_salary) FOR year IN (2022, 2023, 2024));
-- One row per (department, region) combination

This is the most common PIVOT surprise: an unintended grouping column inflates the row count.

-- Best practice: explicit column projection before PIVOT
SELECT *
FROM (
  SELECT department_id, year, total_salary
  FROM   salary_history
)
PIVOT (
  SUM(total_salary)
  FOR year IN (2022, 2023, 2024)
);

Aliasing Pivot Columns

The generated column names default to the pivot values (2022, 2023, 2024). Aliases let you produce friendlier names:

SELECT *
FROM   salary_history
PIVOT (
  SUM(total_salary)
  FOR year IN (
    2022 AS y2022,
    2023 AS y2023,
    2024 AS y2024
  )
);

Result:

You may also alias the aggregate:

PIVOT (
  SUM(total_salary) AS total
  FOR year IN (2022 AS y22, 2023 AS y23, 2024 AS y24)
)

This produces columns like Y22_TOTAL, Y23_TOTAL, Y24_TOTAL — useful when there are multiple aggregates (see next).

Multiple Aggregates

PIVOT can compute several aggregates at once. The resulting columns are named <value>_<aggregate-alias>.

SELECT *
FROM   salary_history
PIVOT (
  SUM(total_salary) AS total,
  COUNT(*)          AS n
  FOR year IN (2023, 2024)
);

Result:

Useful for side-by-side metrics: "total revenue and number of orders per region".

Pivoting on Multiple Columns

You can pivot on a tuple of columns by listing them in FOR (...) IN ((...)):

sales:

SELECT *
FROM   sales
PIVOT (
  SUM(amount)
  FOR (region, quarter) IN (
    ('E', 'Q1') AS east_q1,
    ('E', 'Q2') AS east_q2,
    ('W', 'Q1') AS west_q1,
    ('W', 'Q2') AS west_q2
  )
);

Result:

Each tuple becomes its own column.

UNPIVOT — Columns Back to Rows

UNPIVOT reverses the operation. Given the wide-format salary_by_year:

salary_by_year:

SELECT *
FROM   salary_by_year
UNPIVOT (
  total_salary
  FOR year IN (y2022 AS 2022, y2023 AS 2023, y2024 AS 2024)
);

Result:

The clauses mean:

• total_salary — the output column that will hold the cell values
• FOR year — the output column that will hold the source column names
• IN (y2022 AS 2022, ...) — list which source columns to unpivot, with optional aliases for the new value

EXCLUDE vs INCLUDE NULLS

By default, UNPIVOT skips rows where every source column is NULL. To keep them:

... UNPIVOT INCLUDE NULLS (
  total_salary FOR year IN (y2022, y2023, y2024)
)

For most reporting tasks, the default is correct. Use INCLUDE NULLS when you specifically need to record that "no data was reported for Q3" as a row rather than as missing data.

Dynamic Pivot — When Values Aren't Known in Advance

PIVOT requires the value list to be hard-coded. If you don't know the years until query time, plain PIVOT fails. Two workarounds exist:

Option 1 — Generate the SQL Dynamically

DECLARE
  v_sql VARCHAR2(4000);
  v_in  VARCHAR2(4000);
BEGIN
  SELECT LISTAGG('''' || year_str || ''' AS y' || year_str, ', ')
         WITHIN GROUP (ORDER BY year_str)
  INTO   v_in
  FROM (SELECT DISTINCT TO_CHAR(year) AS year_str FROM salary_history);

  v_sql := 'SELECT * FROM salary_history
            PIVOT (SUM(total_salary) FOR year IN (' || v_in || '))';

  EXECUTE IMMEDIATE v_sql;   -- or open a refcursor with it
END;
/

This approach builds a string and executes it dynamically.

Option 2 — XML-based Dynamic Pivot

Oracle supports an XML keyword that produces an XMLType column with all categories included automatically:

SELECT *
FROM   salary_history
PIVOT XML (
  SUM(total_salary)
  FOR year IN (ANY)
);

Result:

The result is a single XML column per row containing all the pivot values. You then parse it with XMLTABLE. This is awkward to consume but useful when the value list truly is unknown.

In practice, most reports either know the value set in advance (months of the year, fiscal quarters, product categories) or build the SQL dynamically in their application layer.

PIVOT vs CASE Expressions — The Old Way

Before PIVOT existed, this was the only way to pivot in SQL:

SELECT department_id,
       SUM(CASE WHEN year = 2022 THEN total_salary END) AS y2022,
       SUM(CASE WHEN year = 2023 THEN total_salary END) AS y2023,
       SUM(CASE WHEN year = 2024 THEN total_salary END) AS y2024
FROM   salary_history
GROUP BY department_id;

This still works and is the only option on databases without PIVOT. On Oracle, PIVOT is preferred because it's:

• More concise
• Less repetitive
• Easier to read for non-trivial value sets
• Self-documenting (the FOR ... IN list shows the pivot key clearly)

However, CASE is more flexible in two ways:

1. Each column can use a different aggregate (PIVOT only allows one aggregate per pivot expression)
2. Each column can use different filter logic (range comparisons, multi-column predicates, conditional NULLs)

-- CASE handles this; PIVOT cannot:
SELECT department_id,
       AVG(CASE WHEN year = 2024 THEN total_salary END)                 AS avg_24,
       SUM(CASE WHEN year >= 2023 THEN total_salary END)                AS recent_sum,
       COUNT(DISTINCT CASE WHEN year < 2023 THEN employee_id END)       AS legacy_emps
FROM   salary_history
GROUP BY department_id;

Worked Example — Pivot for a Quarterly Report

You need a manager-facing report: each department's salary spend per quarter for the past year.

Source (raw_payments):

Query:

SELECT *
FROM (
  SELECT dept_id,
         'Q' || TO_CHAR(pay_date, 'Q') AS qtr,
         amount
  FROM   raw_payments
  WHERE  pay_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
)
PIVOT (
  SUM(amount) AS total
  FOR qtr IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
)
ORDER BY dept_id;

Result:

Three things to notice:

1. The inline view derives the qtr column from pay_date.
2. Filtering happens before PIVOT — only 2024 rows enter the pivot.
3. The IN list aliases produce clean column names.

Worked Example — Unpivot Survey Responses

Marketing has a wide survey table with one column per question:

survey:

You need to feed an analytics tool that expects one row per (user, question, rating):

SELECT user_id, question, rating
FROM   survey
UNPIVOT (
  rating
  FOR question IN (q1_rating AS 'Q1', q2_rating AS 'Q2', q3_rating AS 'Q3')
);

Result:

Long-format data is much easier to aggregate, filter, and feed into reporting tools.

Common Errors

Interview Corner

-- BAD: department_id, manager_id, hire_date, ... all become grouping keys
SELECT * FROM employees
PIVOT (SUM(salary) FOR department_id IN (10, 20, 30));

-- GOOD: only job_id is a grouper
SELECT *
FROM (SELECT job_id, department_id, salary FROM employees)
PIVOT (SUM(salary) FOR department_id IN (10, 20, 30));

SELECT customer_id, month_name, revenue
FROM   revenue_by_month
UNPIVOT (
  revenue
  FOR month_name IN (
    jan AS 'Jan', feb AS 'Feb', mar AS 'Mar',
    apr AS 'Apr', may AS 'May', jun AS 'Jun'
  )
);

SELECT customer_id,
       TO_DATE(month_name || '-2024', 'Mon-YYYY') AS month_start,
       revenue
FROM   ( /* the UNPIVOT above */ );

Related Topics

• Aggregate Functions — PIVOT is essentially GROUP BY with cross-tabulation
• Subqueries — wrap your source in an inline view to control PIVOT's grouping columns
• Window Functions — compute year-over-year or running totals across pivoted columns
• CTEs & Procedures — extract pivot input into a CTE for readability
• Performance — PIVOT can produce large intermediate sort sets; consider materialising the input