Thursday, August 16, 2007

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!!

7 comments:

Anonymous said...

Great goods from you, man. I've understand your stuff previous to and you're just extremely
magnificent. I actually like what you've acquired here, really like what you are stating and the way in which you say it. You make it entertaining and you still care for to keep it wise. I cant wait to read far more from you. This is really a great site.
Feel free to visit my web-site ; Ldtp.Freedesktop.Org

Anonymous said...

always i used to read smaller content which as well clear their motive, and that is also happening with this paragraph which I am reading
now.
my webpage :: quick cash loan

Anonymous said...

I am truly pleased to glance at this website posts which
includes lots of valuable data, thanks for providing such data.
my page > quickpaydayloans

Anonymous said...

If you want to get a good deal from this paragraph then you have to apply these methods to your won website.
My blog ... cash loans

Anonymous said...

Wow, marvelous weblog format! How lengthy have you been blogging for?
you made running a blog glance easy. The entire glance of your web site is fantastic,
let alone the content!

Visit my web blog :: arsonicgaming.net

Anonymous said...

I do consider all the concepts you have offered to your post.
They're very convincing and can certainly work. Still, the posts are too short for beginners. May just you please extend them a bit from subsequent time? Thanks for the post.

Here is my web-site buy twitter retweets

Anonymous said...

What's up to every one, the contents present at this site are actually amazing for people experience, well, keep up the good work fellows.

Check out my web-site - hintermueller.at