Greens Technologys Whatsapp
Greens Technologys Whatsapp

Oracle / PLSQL: PIVOT Clause

+91-89399 15577

Oracle / PLSQL: PIVOT Clause

This Oracle tutorial explains how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in Oracle/PLSQL is:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

aggregate_function
It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
IN ( expr1, expr2, ... expr_n )
A list of values for column2 to pivot into headings in the cross-tabulation query results.
subquery
It can be used instead of a list of values. In this case, the results of the subquery would be used to determine the values for column2 to pivot into headings in the cross-tabulation query results.

Applies To

The PIVOT clause can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g

Example

Let's look at how to use the PIVOT clause in Oracle.

We will base our example on a table called orders with the following definition:

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

To show you the data for this example, we will select the records from the orders table with the following SELECT statement:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

These are the records in the orders table. We will be using these records to demonstrate how the PIVOT clause works:

order_id customer_ref product_id
50001 SMITH 10
50002 SMITH 20
50003 ANDERSON 30
50004 ANDERSON 40
50005 JONES 10
50006 JONES 20
50007 SMITH 20
50008 SMITH 10
50009 SMITH 20

Now, let's create our cross-tabulation query using the following PIVOT clause:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

In this example, the PIVOT clause would return the following results:

customer_ref 10 20 30
ANDERSON 0 0 1
JONES 1 1 0
SMITH 2 3 0

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Fields to Include

First, we want to specify what fields to include in our cross tabulation. In this example, we want to include the customer_ref and product_id fields. This is done by the following portion of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)

You can list the columns to be included in any order.

Specify Aggregate Function

Next, we need to specify what aggregate function to use when creating our cross-tabulation query. You can use any aggregate such as SUM, COUNT, MIN, MAX, or AVG functions.

In this example, we are going to use the COUNT function. This will count the number of product_id values that meet our criteria. This is done by the following portion of the statement:

PIVOT
(
  COUNT(product_id)

Specify Pivot Values

Finally, we need to specify what pivot values to include in our results. These will be used as the column headings in our cross-tabulation query. You can use either be a list of values enclosed in parentheses or a subquery to specify the pivot values.

In this example, we are going to return only the following product_id values: 10, 20, 30. These values will become our column headings in our cross-tabulation query. Also, note that these values are a finite list of the product_id values and will not necessarily contain all possible values.

This is done by the following portion of the statement:

  FOR product_id IN (10, 20, 30)
)

Now when we put it all together, we get the following pivot table:

customer_ref 10 20 30
ANDERSON 0 0 1
JONES 1 1 0
SMITH 2 3 0


  • 100% Practical Training

  • Hands
    On
    Training

  • 100%
    Job
    Gurantee


Our Reviews 5 Star Rating: Recommended - Best IT Training in Chennai

5  out of 5  based on 12263 ratings.

Google          5 Star Rating: Recommended - Best IT Training in Chennai

Facebook      5 Star Rating: Recommended - Best IT Training in Chennai

UrbanPro       5 Star Rating: Recommended - Best IT Training in Chennai

Sulekha         5 Star Rating: Recommended - Best IT Training in Chennai

Yet5              5 Star Rating: Recommended - Best IT Training in Chennai

Justdial          5 Star Rating: Recommended - Best IT Training in Chennai


Oracle SQL Training in Chennai

Oracle SQL Training in Chennai
best Oracle SQL Training center in chennai

"I thought I knew Oracle SQL until I took this course. My company sent me here against my will. It was definitely worth and I found out how many things I was doing wrong. Karthik is awesome. but i got a lot inspired by you. I will keep in touch and will always try to learn from you as much as I can. Thanks once again Karthik"


Greens Technologys Overall Reviews


Greens Technologys Overall Reviews

5 out of 5 based on 17,981 ratings. 17,981 user reviews.





Relevant Courses