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.
Beekeeper Studio Is An Open Source Oracle GUI
In my role I cover all legacy code, and brand new code, using MSSQL/MariaDB/Oracle/Postgres and SQLite. I think I have now found the tool that covers all my needs in one tool. - Chris Pink
Beekeeper Studio is a fast, intuitive and easy to use Oracle client. It supports loads of other databases too, and works great on Windows, Mac and Linux.
What Oracle Users Say About Beekeeper Studio
In my role i cover all legacy code, and brand new code, using MSSQL/Mariadb/Oracle/Postgres and sqlite, i think i have now found the tool that cover all my needs in one tool
The best thing about Beekeeper Studio is the clean interface and presentation of data. It’s the best tool available that I know of. I use Postgres, MySQL, SQLite, Oracle and MS SQL as an engineer and Beekeeper does the lot with a very smooth and spacious natural intuitive interface. If you short list Beekeper Studio I’m pretty sure it will become your go-to.