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
   ) ;

Kiran P

Published on 05-Dec-2020 06:00:42

Previous Page Print Page