«Back to Home

Oracle Jump Start
Topics

How To Use The PIVOT CLAUSE In Oracle

Description
 
Pivot Clause allows you to write a cross-tabulation query in Oracle. Pivot clause takes the data in separate row, aggregates it and converts it into column.
 
Syntax
 
The syntax for the Pivot clause in Oracle is given below-
  1. Select  
  2. *  
  3. from  
  4. (  
  5. select  
  6. column1,  
  7. column2…column n  
  8. from  
  9. tables  
  10. where  
  11. conditions  
  12. ) PIVOT (  
  13. aggregate_function(column 2) for column 2 IN(expr1, expr2, ….Expr_n) | subquery  
  14. )  
  15. ORDER BY  
  16. expression [ASC | DESC];   
NOTE: Pivot clause can be used in the following versions in Oracle-
               Oracle 11g, Oracle 12c.
 
Example
 
This example shows, how to use the Pivot clause in Oracle.
  1. Create table customer (  
  2. customer_id number(25),  
  3. customer_name vachar2(50),  
  4. product_code varchar2(5),  
  5. quantity number(50),  
  6. constraint customer_pk primary key(customer_id)  
  7. );  
Inserting values
  1. Insert into customer(  
  2. customer_id, customer_name, product_code,  
  3. quantity  
  4. )  
  5. values  
  6. (101, 'SMITH', 'A1', 10);  
  7. Insert into customer(  
  8. customer_id, customer_name, product_code,  
  9. quantity  
  10. )  
  11. values  
  12. (102, 'JONES', 'B2', 20);  
  13. Insert into customer(  
  14. customer_id, customer_name, product_code,  
  15. quantity  
  16. )  
  17. values  
  18. (103, 'ANDERSON', 'C3', 30);  
  19. Insert into customer(  
  20. customer_id, customer_name, product_code,  
  21. quantity  
  22. )  
  23. values  
  24. (104, 'JONES', 'B4', 40);  
  25. Insert into customer(  
  26. customer_id, customer_name, product_code,  
  27. quantity  
  28. )  
  29. values  
  30. (105, 'ANDERSON', 'K4', 20);  
  31. Insert into customer(  
  32. customer_id, customer_name, product_code,  
  33. quantity  
  34. )  
  35. values  
  36. (106, 'JONES', 'C4', 40);  
  37. Insert into customer(  
  38. customer_id, customer_name, product_code,  
  39. quantity  
  40. )  
  41. values  
  42. (107, 'SMITH', 'B3', 20);  
  43. Insert into customer(  
  44. customer_id, customer_name, product_code,  
  45. quantity  
  46. )  
  47. values  
  48. (108, 'JONES', 'D1', 10);   
1 
 
Now, you will select the records from the order table with select statement.
 
Syntax
  1. select  
  2. column1,  
  3. column2,  
  4. column3  
  5. from  
  6. table_name  
  7. Order by  
  8. column_name;  
Example
  1. select  
  2. customer_id,  
  3. customer_name,  
  4. product_code  
  5. from  
  6. customer  
  7. order by  
  8. customer_id;  
2 
 
Now, you will create our cross tabulation query using the following pivot clause.
 
Syntax
  1. select  
  2. *  
  3. from  
  4. (  
  5. select  
  6. column1,  
  7. column2  
  8. from  
  9. table_name  
  10. ) PIVOT (  
  11. COUNT (column_name) for column_name IN(column_values)  
  12. )  
  13. order by  
  14. customer_name;  
Example
  1. select  
  2. *  
  3. from  
  4. (  
  5. select  
  6. customer_name,  
  7. quantity  
  8. from  
  9. customer  
  10. ) PIVOT (  
  11. COUNT(quantity) for quantity IN (10, 20, 30)  
  12. )  
  13. order by  
  14. customer_name;  
3 
 
Now, let’s explain, how Pivot clause works.
Let’s break apart Pivot clause.
First, we want to specify what fields to include in our cross tabulation.
In this example, we want to include the customer_name and product_code fields. This is done by the portion of the statement, given below-
  1. (  
  2. Select  
  3. customer_name,  
  4. product_code  
  5. from  
  6. customer  
  7. )  
You can list the columns to be included in any order.
 
4 
 
Now, we will specify aggregate function and Pivot values.
You can use any aggregate function such as SUM, COUNT, MIN, MAX or AVG functions.
 
In this example, we will choose the count function. This function will count the number of product_code values.
 
Statement
  1. pivot (  
  2. count (product_id)  
Finally, we need to specify, what are pivot values.
 
In this example, we are going to return only the following product_code values. These values become a column heading in our cross-tabulation query.
Statement
  1. for product_code IN(10, 20, 40)  
  2. )  
5 
 
Summary
 
Thus, we learnt, Pivot Clause allows you to write a cross-tabulation query in Oracle. Pivot clause takes the data in separate row, aggregates it and converts it into the column. We learnt, how to use this clause with the examples.