In Oracle, PIVOT operation can be used to convert rows into columns. The answer is: yes, supported from the 11g version; its basic syntax is SELECT FROM (source query) PIVOT (aggregation function (column) FOR column to be converted IN (value 1, value 2, ...)); for example, a quarterly perspective of sales table, using SUM(amount) FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2) can obtain employee quarterly sales; when using PIVOT, you need to note: the aggregate function must be used, the value in the IN clause must be known in advance, the string value must be case sensitive, and column alias can be specified by AS; multiple aggregations such as SUM(amount) AS sum_amt, COUNT() AS count_trans will generate a combined column name; it is impossible to see through dynamic perspective directly, and SQL needs to be generated dynamically by combining LISTAGG and EXECUTE IMMEDIATE through PL/SQL; compared with the traditional CASE statement method, PIVOT syntax is more concise and clear; summary: When rows of known categories need to be converted into columns for report display, otherwise dynamic SQL needs to be implemented.

In Oracle, the PIVOT operation allows you to rotate rows into columns — essentially transforming unique values from one column into multiple columns in the output. This is useful for summarizing and reporting data in a more readable, cross-tabular format.

Oracle introduced the PIVOT clause starting from version 11g, so make sure your database supports it.

? Basic Syntax of PIVOT

 SELECT *
FROM (
  -- Your base query (source data)
)
PIVOT (
  -- Aggregation function and column to pivot
  aggregate_function(column_to_aggregate)
  FOR column_to_pivot IN (value1, value2, ...)
);

? Example: Sales Data by Quarter

Suppose you have a table called sales :

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

INSERT INTO sales VALUES ('Alice', 'Q1', 1000);
INSERT INTO sales VALUES ('Alice', 'Q2', 1200);
INSERT INTO sales VALUES ('Bob', 'Q1', 800);
INSERT INTO sales VALUES ('Bob', 'Q2', 1500);

You want to show each employee's sales in separate columns for each quarter.

? Using PIVOT

 SELECT *
FROM (
  SELECT employee, quarter, amount
  FROM sales
)
PIVOT (
  SUM(amount) -- Aggregation
  FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2)
);

? Output:

Note: 'Q1' AS Q1 means the value 'Q1' becomes a column named Q1 .

?? Key Points When Using PIVOT

• Aggregation is required : Even if there's only one row per group, you must use an aggregate function like SUM , MAX , COUNT , etc.
• Know values needed : The values in the IN clause must be known at query time. You can't dynamically pivot without dynamic SQL.
• Case sensitivity : String values in the IN list are case-sensitive and must match exactly (and be quoted if they are string literals).
• Column aliases : Use AS to name the pivoted columns.

? Using Multiple Aggregations

You can pivot multiple aggregations:

 PIVOT (
  SUM(amount) AS sum_amt,
  COUNT(*) AS count_trans
  FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2)
)

Output:

This creates composite column names based on the aggregation alias and the pivot value.

? Dynamic PIVOT?

Oracle SQL does not support dynamic lists in PIVOT directly. If you don't know the values in advance (eg, dynamic product names), you need to:

1. Query the distinct values first.
2. Build the SQL string dynamically.
3. Execute it using PL/SQL with EXECUTE IMMEDIATE .

Example (outline in PL/SQL):

 DECLARE
  sql_stmt CLOB;
BEGIN
  SELECT 'SELECT * FROM sales PIVOT (SUM(amount) FOR quarter IN (' ||
         LISTAGG('''|| quarter || ''' AS ' || quarter, ', ')
         WITHIN GROUP (ORDER BY quarter) ||
         '))'
  INTO sql_stmt
  FROM (SELECT DISTINCT quarter FROM sales);

  EXECUTE IMMEDIATE sql_stmt;
END;
/

Note: This requires careful handling and is typically used in reports or apps.

? PIVOT vs. Old-School CASE (Pre-11g Style)

Before PIVOT , people used CASE or DECODE :

 SELECT
  Employee,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2
FROM sales
GROUP BY employee;

It works, but PIVOT is cleaner and more readable when dealing with multiple categories.

? Summary

Use PIVOT in Oracle when:

• You want to convert row values into columns.
• You're doing reporting or summaries (eg, by month, category, region).
• You know the pivot values in advance.

Avoid or use dynamic SQL when:

• The pivot values change frequently.
• You need full automation over unknown categories.

Basically, PIVOT makes cross-tab queries much easier and readable in Oracle 11g and later. Just remember: aggregate function , FOR column , and IN list of values .

The above is the detailed content of How to use PIVOT in Oracle?. For more information, please follow other related articles on the PHP Chinese website!