Oracle PIVOT

Summary: in this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to generate result sets in crosstab format.

Introduction to Oracle PIVOT clause

Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.

The following illustrates the basic syntax of the Oracle PIVOT clause:

SELECT 
    select_list
FROM 
    table_name
PIVOT [XML] ( 
    pivot_clause
    pivot_for_clause
    pivot_in_clause 
);

In this syntax, following the PIVOT keyword are three clauses:

• pivot_clause specifies the column(s) that you want to aggregate. The pivot_clause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivot_in_clause.
• pivot_for_clause specifies the column that you want to group or pivot.
• pivot_in_clause defines a filter for column(s) in the pivot_for_clause. The aggregation for each value in the pivot_in_clause will be rotated into a separate column.

Oracle PIVOT example

Let’s create a new view named order_stats that includes product category, order status, and order id for demonstration.

CREATE VIEW order_stats AS
SELECT 
    category_name, 
    status, 
    order_id
FROM 
    order_items
INNER JOIN orders USING (order_id)
INNER JOIN products USING (product_id)
INNER JOIN product_categories USING (category_id);

Here is the partial data from the order_stats view:

SELECT * FROM order_stats;

This example uses the PIVOT clause to return the number of orders for each product category by order status:

SELECT * FROM order_stats
PIVOT(
    COUNT(order_id) 
    FOR category_name
    IN ( 
        'CPU',
        'Video Card',
        'Mother Board',
        'Storage'
    )
)
ORDER BY status;

In this example:

• The COUNT(order_id) is the pivot_clause.
• FOR category_name is the pivot_for_clause.

And here is the pivot_in_clause:

IN (
    'CPU',
    'Video Card',
    'Mother Board',
    'Storage'
)

The COUNT() function returns the number of orders by category and order status. The query uses the values specified in the pivot_in_clause for the column headings of the result set.

Here is the output:

Aliasing pivot columns

In the previous example, Oracle used product categories to generate pivot column names. On the other hands, you can alias one or more columns in the pivot_clause and one or more values in the pivot_in_clause.

Generally, Oracle uses the following convention to name the pivot columns based on aliases:

The following statement uses the query example above with the aliases:

SELECT * FROM order_stats
PIVOT(
    COUNT(order_id) order_count
    FOR category_name
    IN ( 
        'CPU' CPU,
        'Video Card' VideoCard, 
        'Mother Board' MotherBoard,
        'Storage' Storage
    )
)
ORDER BY status;

Here is the result set:

As you can see, the pivot column names follow the below naming convention:

pivot_in_clause alias || '_' || pivot_clause alias

Note that if you use more than one aggregate function in the pivot_clause, you must provide aliases for at least one of the aggregate functions.

Pivoting multiple columns

In the previous example, you have seen that we used one aggregate function in the pivot_clause. In the following example, we will use two aggregate functions.

First, alter the order_stats view to include the order value column:

CREATE OR REPLACE VIEW order_stats AS
SELECT 
    category_name, 
    status, 
    order_id, 
    SUM(quantity * list_price) AS order_value
FROM 
    order_items
INNER JOIN orders USING (order_id)
INNER JOIN products USING (product_id)
INNER JOIN product_categories USING (category_id)
GROUP BY 
    order_id, 
    status, 
    category_name;

Second, query data from the new order_stats view:

SELECT * FROM order_stats;

Third, use PIVOT clause to return the number of orders and order values by product category and order status:

SELECT * FROM order_stats
PIVOT(
    COUNT(order_id) orders,
    SUM(order_value) sales
    FOR category_name
    IN ( 
        'CPU' CPU,
        'Video Card' VideoCard, 
        'Mother Board' MotherBoard,
        'Storage' Storage
    )
)
ORDER BY status;

Here is the output:

As you can see from the output, the number of pivot columns is doubled, combining category_name with orders and sales.

Finally, use status as the pivot columns and category_name as rows:

SELECT * FROM order_stats
PIVOT(
    COUNT(order_id) orders,
    SUM(order_value) sales
    FOR status
    IN ( 
        'Canceled' Canceled,
        'Pending' Pending, 
        'Shipped' Shipped
    )
)
ORDER BY category_name;   

The following picture shows the output:

Oracle PIVOT with subquery

You cannot use a subquery in the pivot_in_clause. The following statement is invalid and causes an error:

SELECT * FROM order_stats
PIVOT(
    COUNT(order_id) orders,
    SUM(order_value) sales
    FOR category_name
    IN ( 
       SELECT category_name 
       FROM product_categories
    )
)
ORDER BY status;

Here is the error message:

ORA-00936: missing expression

This restriction is relaxed with the XML option:

SELECT * FROM order_stats
PIVOT XML (
    COUNT(order_id) orders,
    SUM(order_value) sales
    FOR category_name
    IN ( 
       SELECT category_name 
       FROM product_categories
    )
)
ORDER BY status;    

This picture is the output:

Here is the sample of one PivotSet:

<PivotSet>
    <item>
        <column name="CATEGORY_NAME">CPU</column>
        <column name="ORDERS">13</column>
        <column name="SALES">4122040.7</column>
    </item>
    <item>
        <column name="CATEGORY_NAME">Mother Board</column>
        <column name="ORDERS">12</column>
        <column name="SALES">679121.39</column>
    </item>
    <item>
        <column name="CATEGORY_NAME">RAM</column>
        <column name="ORDERS">0</column>
        <column name="SALES" />
    </item>
    <item>
        <column name="CATEGORY_NAME">Storage</column>
        <column name="ORDERS">14</column>
        <column name="SALES">3023747.6</column>
    </item>
    <item>
        <column name="CATEGORY_NAME">Video Card</column>
        <column name="ORDERS">9</column>
        <column name="SALES">1677597.4</column>
    </item>
</PivotSet>

To view XML in the output grid of from the SQL Developer, you follow these steps to set it up:

1)  From the Tool menu, select Preferences

2) Under Database > Advanced, check the option Display XML Value in Grid

The XML output format is not the same as non-XML pivot one. For each value specified in the pivot_in_clause, the subquery returns a single XML string column.

The XML string for each row contains aggregated data corresponding to the implicit GROUP BY value of that row e.g., number of orders ( ORDERS) and total sales ( SALES).

When you use a subquery in the pivot_in_clause, Oracle uses all values returned by the subquery for pivoting.

Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error. If you are not sure whether the subquery returns a list of distinct values or not, you can use he DISTINCT keyword in the subquery.

In this tutorial, you have learned how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.