Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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 ) ;
13K+ Views
