Using PIVOT Clause in Oracle

This article is written about how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause lets in you to write a cross-tabulation question starting in Oracle 11g. This potential that you can combination your consequences 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 characteristic such as SUM, COUNT, MIN, MAX, or AVG functions. IN ( expr1, expr2, … expr_n ) A listing of values for column2 to pivot into headings in the cross-tabulation question results. subquery It can be used as an alternative of a list of values. In this case, the outcomes of the subquery would be used to determine the values for column2 to pivot into headings in the cross-tabulation question results.

Applies To

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

Oracle 12c, Oracle 11g

Example

Let’s seem at how to use the PIVOT clause in Oracle.

We will base our instance on a table referred to as 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 archives from the orders desk with the following SELECT statement:

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

These are the files in the orders table. We will be using these files 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 question 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 zero zero 1 JONES 1 1 0 SMITH 2 3 zero

Now, let’s ruin aside the PIVOT clause and give an explanation for how it worked.

Specify Fields to Include

First, we desire to specify what fields to encompass in our cross tabulation. In this example, we favor to consist of the customer_ref and product_id fields. This is achieved through the following element of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)

You can listing the columns to be covered in any order.

Specify Aggregate Function

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

In this example, we are going to use the COUNT function. This will rely the quantity of product_id values that meet our criteria. This is achieved via the following portion of the statement:

PIVOT
(
  COUNT(product_id)

Specify Pivot Values

Finally, we need to specify what pivot values to encompass in our results. These will be used as the column headings in our cross-tabulation query. You can use both 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 grow to be our column headings in our cross-tabulation query. Also, be aware that these values are a finite list of the product_id values and will no longer always include all feasible values.

This is finished with the aid of the following element 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 zero zero 1 JONES 1 1 0 SMITH two 3 0