Converting rows to columns using Oracle Pivot clause

The Oracle introduced the PIVOT clause from oracle 11g. This makes converting rows to column easy. The value in the new columns must be the result of an aggregate function like count, sum, min, etc. The PIVOT operator takes data in separate rows and aggregates it. Then converts that aggregated values into columns. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.

Syntax / Sample

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

We can have a look into a sample table DEMO_ORDER_DETAILS for understanding briefly.

We are going to apply the PIVOT operator in this data. Assume that you want the summary of the sales product-wise.

You can use this query

SELECT * FROM (
SELECT SALES_MAN,PRODUCT_ID,QUANTITY 
FROM DEMO_ORDER_DETAILS)
PIVOT
( SUM(QUANTITY)
FOR PRODUCT_ID IN (10 ,20,30));

If you want to use an alias name for each product available in the table then you can use it like this.

select * from (
select SALES_MAN,PRODUCT_ID,QUANTITY
from DEMO_ORDER_DETAILS)
pivot 
( sum(QUANTITY)
for PRODUCT_ID in ('10' PEN ,20 BOOK ,30 BAG));

You can use multiple aggregate functions within the same pivot clause. You have to make sure that there should be an alias name for the aggregated column. Otherwise, there is a chance to throw the error as ‘ORA-00918: column ambiguously defined’.

SELECT
*
FROM
DEMO_ORDER_DETAILS PIVOT (
COUNT ( ORDER_ID ) ORDERS, 
SUM ( QUANTITY ) SALES
FOR PRODUCT_ID
IN ( 10 PEN, 20 BOOK, 30 BAG )
);

Here you can see the number of columns has been increased. The oracle will automatically combine each combination as you saw in the example.

PEN_ORDERS and PEN_SAILED,

BOOK_ORDERS and BOOK_SAILED,

BAG_ORDERS and BAG_SAILED

Likewise, we can have multiple aggregation columns in the PIVOT clause

SELECT * 
FROM 
(SELECT SALES_MAN,ORDER_ID,PRODUCT_ID,QUANTITY,UNIT_PRICE FROM DEMO_ORDER_DETAILS)
PIVOT 
( COUNT(ORDER_ID) ORDERS,
SUM(QUANTITY) SALED,
SUM(QUANTITY * UNIT_PRICE) TOTAL_AMOUNT
FOR PRODUCT_ID IN (10 PEN ,20 BOOK ,30 BAG));

Note that you can directly use subqueries in the pivot clause. The subquery will work only when you are using XML. So you have to use the PIVOT XML clause instead of the PIVOT.

Jabir Muhammed