- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to PIVOT results in Oracle?
Problem:
You want to PIVOT results in Oracle.
Solution
The PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time,allow you to run an aggregation function on the data.
PIVOT is especially useful to look at overall trends in large amounts of data.
We will be using sales data to demonstrate the usage.
Example
SELECT * FROM sales;
Output
12008 12 1998-02-28 6 37 552 1898.88 12008 18 1998-02-28 6 37 463 1592.72 12008 20 1998-02-28 6 37 2430 8359.2 12008 25 1998-03-31 6 36 3387 12091.59 12008 26 1998-01-31 6 37 2381 8190.64
Syntax for PIVOT.
Example
SELECT * FROM ( inner_query ) PIVOT ( aggregate_function FOR pivot_column IN (list_of_values) ) ORDER BY…;
We will use PIVOT to see trends in sales of types of products over a period of months. Below is the example for the same.
Example
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04) = 1998 ) PIVOT ( SUM(amount_sold) FOR prod_id IN (12008 , 12010 , 12011 ) ) ORDER BY month_val;
Output
01 145714.96 1039519.04 1719280.2 02 149309.76 1067082.24 1758706.44 03 1072859.97 8045916.84 13191209.28 04 154280.56 1106052.23 1826847.36 05 155322.88 1119728.52 1848467.64 06 1166464.25 8477308.28 13882822.54 07 157737.76 1145882.33 1891410.4 08 167073.92 1214760.69 2010620.5 09 1274290.22 8932017.99 15038327.33 10 160988.56 1180869.5 1956298.5 11 159736.4 1189188 1964635.2 12 1227572.64 8795186.2 14774142.24
We can also add multiple tables to the PIVOT. Here is an example.
Example
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04) = 1998 ) PIVOT ( SUM(amount_sold) FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));
Further more, we can also have multiple aggregate functions in our PIVOT query.
Example
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04)= 1998 ) PIVOT ( SUM(amount_sold),AVG(amount_sold) AS avg_amount FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));
Table structure for above problem statement:
Example
CREATE TABLE "SALES" ( "PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" VARCHAR2(20) NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE ) ;
- Related Articles
- How to UNPIVOT results in Oracle?
- How to cache query results in Oracle?
- How to assign ranks to the query results in Oracle?
- How to clear old items in pivot table?
- How to alternate row colour in an Excel Pivot Table?
- How to Add Secondary Axis to a Pivot Chart in Excel?
- How to add a calculated field to a pivot table in Excel?
- How to add multiple fields into a pivot table in Excel?
- How to traverse Hierarchical data in Oracle?
- How to use DATETIME functions in Oracle?
- How to display open cursors in Oracle?
- How to concatenate results in MongoDB?
- How to find and replace text in Oracle?
- How to perform string aggregation/concatenation in Oracle?
- How to change the JOIN order in Oracle?
