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.
A Database Manager That Is Modern, Fast, & Easy To Use
Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan
I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.
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.
