Creating Pivot Tables in Oracle Database Using PIVOT and UNPIVOT Operators

Pivot tables are a powerful tool in data analysis, allowing you to organize and summarize large sets of data in a meaningful way. In Oracle Database, you can create pivot tables using the PIVOT and UNPIVOT operators.

Using PIVOT

To create a pivot table, you first need to have a table with your source data. Let’s say you have a table called “sales” with columns for the date, product, and sales amount. To create a pivot table, you would use the following SQL statement:

SELECT *
FROM sales
PIVOT (
    SUM(sales_amount)
    FOR product IN ('Product A', 'Product B', 'Product C')
);

This statement creates a pivot table that shows the total sales amount for each of the specified products. The pivot table will have one column for each product, and the rows will show the sales amount for each product.

Filter with WHERE

If you want to show the sales data for a specific date range, you can add a WHERE clause to the SQL statement:

SELECT *
FROM sales
WHERE date BETWEEN '2022-01-01' AND '2022-12-31'
PIVOT (
    SUM(sales_amount)
    FOR product IN ('Product A', 'Product B', 'Product C')
);

This statement will show the total sales amount for each product during the specified date range.

Multiple measures using UNPIVOT

Another common use for pivot tables is to show data for multiple measures. For example, you might want to see the total sales amount, the average sales amount, and the number of sales for each product. To do this, you can use the UNPIVOT operator in combination with the PIVOT operator:

SELECT *
FROM (
    SELECT
        date,
        product,
        sales_amount,
        AVG(sales_amount) OVER (PARTITION BY product) AS avg_sales,
        COUNT(*) OVER (PARTITION BY product) AS num_sales
    FROM sales
)
UNPIVOT (
    value FOR measure IN (sales_amount, avg_sales, num_sales)
)
PIVOT (
    SUM(value)
    FOR product IN ('Product A', 'Product B', 'Product C')
);

This statement creates a pivot table that shows the total sales amount, average sales amount, and number of sales for each product. The pivot table will have three columns for each measure, and the rows will show the values for each product.

Oracle Pivot Table Summary

In conclusion, pivot tables are a powerful tool for organizing and summarizing large sets of data in Oracle Database. To create a pivot table, you can use the PIVOT and UNPIVOT operators in combination with other SQL statements. This allows you to quickly and easily analyze your data and gain valuable insights.