Oracle SQL’s PIVOT clause allows you to transform row data into columns. It’s particularly useful when summarizing data across multiple categories, such as months, departments, or product types.

1. What Is Pivoting?

Pivoting is the process of converting rows into columns to better analyze or report on grouped data.

Example Scenario:

Imagine you have a table of sales data:

EMPLOYEE	MONTH	SALES
Alice	Jan	1000
Alice	Feb	2000
Bob	Jan	1500
Bob	Feb	2500

You want to transform this into:

EMPLOYEE	JAN	FEB
Alice	1000	2000
Bob	1500	2500

This is exactly what PIVOT does.

2. Oracle PIVOT Syntax

SELECT * 
FROM (
  SELECT <columns>
  FROM <table>
)
PIVOT (
  <aggregate_function>(<column_to_aggregate>)
  FOR <column_to_pivot>
  IN (<list_of_values>)
);

3. Sample Table for Example

Let’s create a simple table for demonstration:

CREATE TABLE sales (
  employee VARCHAR2(50),
  month    VARCHAR2(10),
  amount   NUMBER
);

INSERT INTO sales VALUES ('Alice', 'JAN', 1000);
INSERT INTO sales VALUES ('Alice', 'FEB', 2000);
INSERT INTO sales VALUES ('Bob', 'JAN', 1500);
INSERT INTO sales VALUES ('Bob', 'FEB', 2500);
INSERT INTO sales VALUES ('Bob', 'MAR', 1000);

COMMIT;

4. Basic PIVOT Example

SELECT *
FROM (
  SELECT employee, month, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR month IN ('JAN' AS Jan, 'FEB' AS Feb, 'MAR' AS Mar)
);

Output:

EMPLOYEE	JAN	FEB	MAR
Alice	1000	2000	NULL
Bob	1500	2500	1000

Explanation:

• SUM(amount) is the aggregation function.
• month is the column whose values will become new columns.
• 'JAN' AS Jan, 'FEB' AS Feb, ... specifies the values to pivot and how the new column should be named.

5. Important Notes

• The values in IN(...) must match actual data values.
• Column aliases (like AS Jan) are optional but improve readability.
• If a pivoted value is missing, Oracle returns NULL.

6. Filtering with Pivot

If you want to filter by employee, you can add a WHERE clause outside the pivot:

SELECT *
FROM (
  SELECT employee, month, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR month IN ('JAN' AS Jan, 'FEB' AS Feb)
)
WHERE employee = 'Bob';

7. Dynamic Pivoting (Advanced)

Oracle SQL doesn’t support fully dynamic pivoting in plain SQL, but it can be achieved using PL/SQL or dynamic SQL.

Example using PL/SQL:

DECLARE
  sql_stmt VARCHAR2(1000);
BEGIN
  sql_stmt := '
    SELECT * FROM (
      SELECT employee, month, amount FROM sales
    )
    PIVOT (
      SUM(amount)
      FOR month IN (''JAN'' AS Jan, ''FEB'' AS Feb)
    )';
  EXECUTE IMMEDIATE sql_stmt;
END;

To build a dynamic list of months, you’d first query them from the data and construct the pivot SQL dynamically.

8. Multiple Aggregates with PIVOT

Oracle supports only one aggregation at a time using the PIVOT clause. If you need multiple aggregates (e.g., SUM and AVG), you can:

• Use multiple PIVOT queries and join them
• Or use conditional aggregation instead

9. Alternatives to PIVOT

If PIVOT seems complex, or you need more flexibility, you can use CASE + GROUP BY manually.

SELECT
  employee,
  SUM(CASE WHEN month = 'JAN' THEN amount END) AS Jan,
  SUM(CASE WHEN month = 'FEB' THEN amount END) AS Feb,
  SUM(CASE WHEN month = 'MAR' THEN amount END) AS Mar
FROM sales
GROUP BY employee;

Same output, and easier to make dynamic if needed.

10. Real-World Use Case: Sales by Region and Month

Assume this data:

REGION	MONTH	SALES
East	Jan	1000
East	Feb	1500
West	Jan	1200
West	Mar	1300

Query:

SELECT *
FROM (
  SELECT region, month, sales
  FROM sales_by_region
)
PIVOT (
  SUM(sales)
  FOR month IN ('JAN' AS Jan, 'FEB' AS Feb, 'MAR' AS Mar)
);

11. Summary

Feature	Description
Purpose	Convert row data into columns
Works With	Aggregation functions (SUM, AVG, COUNT, etc.)
Syntax	PIVOT (agg(column) FOR col IN (...))
Required Oracle	Oracle 11g and above
Values Must Be	Known in advance or dynamically built

12. Best Practices

• Use column aliases to make pivoted columns readable.
• Handle NULLs gracefully using NVL() if needed.
• Avoid too many pivoted columns – it can make the query hard to maintain.
• Use conditional aggregation (CASE + GROUP BY) for dynamic column needs.