PL/SQL PIVOT

In Oracle PL/SQL, the PIVOT operation is used to transpose rows into columns, transforming the result of a SELECT statement into a more readable and compact format. This is particularly useful when you want to pivot data based on a specific column’s values. The PIVOT clause simplifies the process of rotating data in SQL queries.

Syntax

Here’s a brief overview of how to use the PIVOT clause in Oracle PL/SQL:

SELECT *
FROM (
  SELECT column1, column2, pivot_column, value_column
  FROM your_table
)
PIVOT (
  AGGREGATE_FUNCTION(value_column)
  FOR pivot_column IN (value1, value2, ..., valueN)
);

AGGREGATE_FUNCTION: This is typically a SQL aggregate function like SUM, AVG, MAX, MIN, etc. It specifies how to aggregate values when there are multiple occurrences for the same combination of pivot and value columns.

value_column: The column containing the values you want to pivot.

pivot_column: The column whose unique values become the new column headers.

Example

Consider a table named sales_data with columns product, quarter, and revenue. You want to pivot the data to show total revenue for each product in each quarter.

SELECT *
FROM (
  SELECT product, quarter, revenue
  FROM sales_data
)
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
);

In this example, the quarters (‘Q1’, ‘Q2’, ‘Q3’, ‘Q4’) become the new column headers, and the total revenue for each product is displayed under the corresponding quarter.

Dynamic Pivot

In some cases, the values for the pivot column might be dynamic. In such scenarios, you can use dynamic SQL to construct the PIVOT query at runtime based on the available values.

DECLARE
  pivot_query VARCHAR2(1000);
BEGIN
  pivot_query := 'SELECT * FROM (
                   SELECT product, quarter, revenue
                   FROM sales_data
                 )
                 PIVOT (
                   SUM(revenue)
                   FOR quarter IN (''Q1'', ''Q2'', ''Q3'', ''Q4''))';

  EXECUTE IMMEDIATE pivot_query;
END;

Note

The PIVOT operation is available in Oracle Database 11g and later versions.
Be cautious with dynamic SQL due to potential SQL injection vulnerabilities; ensure that input values are properly sanitized.

Using the PIVOT clause in Oracle PL/SQL can significantly simplify result set transformations, especially when dealing with data that is more naturally represented in a cross-tabular format.

In essence, PL/SQL PIVOT simplifies the process of converting data from a vertical to a horizontal orientation, making it easier to compare and analyze information. By specifying the desired pivot columns and the corresponding aggregation functions, users can create concise and insightful reports that facilitate better decision-making. This feature is widely employed in business intelligence, data warehousing, and reporting applications, offering a flexible and efficient way to organize and present complex datasets.