Pivot or Unpivot? no big deal from 11g

pivoting and unpivoting resultsets - a kids game from 11g onwards

you dont need to be a sql guru to figure out how to transform rows into columns and vice versa.

here comes PIVOT and UNPIVOT clause of 11g.

I dont think I need to explain more..the below text is straight copy & paste from documentation.

Using PIVOT and UNPIVOT: Examples

The oe.orders table contains information about when an order was placed (order_date), how it was place (order_mode), and the total amount of the order (order_total), as well as other information. The following example shows how to use the PIVOT clause to pivot order_mode values into columns, aggregating order_total data in the process, to get yearly totals by order mode:

CREATE TABLE pivot_table AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));

SELECT * FROM pivot_table ORDER BY year;

     YEAR      STORE   INTERNET
---------- ---------- ----------
     1990    61655.7
     1996     5546.6
     1997        310
     1998   309929.8   100056.6
     1999  1274078.8  1271019.5
     2000   252108.3   393349.4

6 rows selected.

The UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the INCLUDE NULLS clause.

SELECT * FROM pivot_table
 UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1996 direct       5546.6
     1997 direct          310
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4
9 rows selected.

SELECT * FROM pivot_table
 UNPIVOT INCLUDE NULLS
   (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1990 online
     1996 direct       5546.6
     1996 online
     1997 direct          310
     1997 online
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4

12 rows selected.

thats neat!!