Oracle has introduced a new SQL operator in 11g called PIVOT. The PIVOT operator allows query results to be presented in pivot form. Oracle stores data in a manner that is optimized for performance. Sometimes, this makes the data hard to read. Using the PIVOT operator allows the data to be displayed in a more readable and understandable format. The basic syntax for using the PIVOT operator is:

ELECT *
  FROM (SELECT column_1, column_2, column_3 
         FROM table_name)
 PIVOT (Aggregate_function(column_1) 
        FOR column_2 
         IN (value_1, value_2,…)
         ORDER BY column_3

In order to SELECT specific columns, a subquery has to be used in the FROM clause with the specific columns that will be used in the PIVOT operator. In this query, column_3 would be the values in the left column of the results, column_2 would be the top row, and the value returned from the aggregate function would populate the rest of the rows and columns. Each value from the aggregate function will correlate to a value in column_2 and column_3.

To visualize this, a table needs to be created (the SQL for this table can be found in the attached .sql file):

CREATE TABLE pivot_practice
   (customer_id NUMBER, product VARCHAR(25), price NUMBER);

INSERT INTO pivot_practice 
     VALUES (10, 'Pizza', 5);

INSERT INTO pivot_practice 
     VALUES (15, 'Video Game', 25);

INSERT INTO pivot_practice 
     VALUES (20, 'TV', 70);

INSERT INTO pivot_practice 
     VALUES (10, 'Pizza', 5);

INSERT INTO pivot_practice 
     VALUES (25, 'Video Game', 25);

INSERT INTO pivot_practice 
     VALUES (15, 'TV', 70);

INSERT INTO pivot_practice 
     VALUES (10, 'Video Game', 25);

INSERT INTO pivot_practice 
VALUES (15, 'Pizza', 5);

To figure out which customer bought what items, and how much they spent on each item, the PIVOT operator can be used:

SELECT *
  FROM (SELECT customer_id, product, price 
          FROM pivot_practice)
 PIVOT (SUM(price) 
        FOR product 
         IN ('Pizza', 'Video Game', 'TV'))
         ORDER BY customer_id;

Here is the result of running this query:

CUSTOMER_ID       'Pizza'           'Video Game'      'TV'                   
----------------- ----------------- ----------------- ----------------- 
10                10                25                                            
15                5                 25                70                     
20                                                    70                     
25                                  25

XML

A negative about the PIVOT operator is that the values have to be hard coded. A way around this is to add the XML tag after PIVOT:

SELECT *
  FROM (SELECT column_1, column_2, column_3 
          FROM table_name)
 PIVOT XML (Aggregate_function(column_1) 
            FOR column_2 
             IN (ANY)
             ORDER BY column_3

The ANY keyword includes every value that is is column_2. Another way to not hard code the values in is by adding a subquery for the values:

SELECT *
  FROM (SELECT column_1, column_2, column_3 
          FROM table_name)
 PIVOT XML (Aggregate_function(column_1) 
            FOR column_2 
             IN (SELECT column_2 
                   FROM table_name)
                   ORDER BY column_3

The output from using XML in a PIVOT query is in XML form:

SELECT *
  FROM (SELECT customer_id, product, price 
           FROM pivot_practice)
 PIVOT XML (SUM(price) 
            FOR product 
             IN (ANY))
 ORDER BY customer_id;

Customer_id  Product_XML
-----------  ------------
10           Pizza
             10
             Video Game25
15           Pizza
             5
             TV70Video Game25
             
20           TV
             70
25           Video Game
25

Comment