Pivot, extract and more fun SQL stuff

I was thinking back to the Warehouse Builder days and the pivot and unpivot operators we built into the tool. Wanted to check out the SQL commands in 11g and see how that works, so I dug around in the documentation.

First thing I noticed is that we use EXTRACT in the pivot example, and I somehow missed the fact that we have that as syntax in Oracle. I haven't figured out when we introduced that (if anyone has the answer let me know)...

Anyways, and in no means to be complete and exact here, lets first quickly look at EXTRACT. It is kind of doing a format function on a date.

SELECT extract(year from order_date) year
from oe.orders
/

select to_char(order_date, 'yyyy') year
from oe.orders
/

The net result of the latter is a text string of course, the former keeps the year a number, which may or may not be an additional advantage. Just as a cute one, I figured it is worthwhile to mention.

Now on to PIVOT, which did come into the database as of 11.1.0.6.

For the example I played around with the orders table in OE (as I did with the extract stuff and as the database documentation does). Here I'm getting for the year 2000 the total order values for each way of ordering something from the store.

A basic query gets me something like this (notice I went overboard with the extract...):

SELECT extract(month from order_date) month, order_mode, sum(order_total)
from oe.orders
where EXTRACT(YEAR FROM order_date) = 2000
group by EXTRACT(month FROM order_date), order_mode
order by EXTRACT(month FROM order_date)
/

initial_no_pivot_results.JPG

Point here being of course that you get a line for each month (month number 1, 2 etc) for each of the ways of purchasing. With Pivot I can now make a single line for month, and list the numbers for each purchase method next to each other:

initial_pivot_results.JPG

Not only is the query relatively simple, it is also much easier to see that there are months in which one or the other sales mechanism has no sales (I know it is a silly demo schema, but again, you get the point):

Select * from
( SELECT extract(month from order_date) month, order_mode, order_total
from oe.orders
where EXTRACT(YEAR FROM order_date) = 2000
)
PIVOT
(SUM(order_total) for order_mode in ('direct' as store, 'online' as internet))
order by 1
/

So with this small extension to the statement you actually get to do some cool stuff.

If I push this a little further and change the outer query into something more useful, I can create an interesting report (and it shows how to refer to the pivot results):

Select month, store, internet, nvl(store,0) + nvl(internet,0) total from
( SELECT extract(month from order_date) month, order_mode, order_total
from oe.orders
where EXTRACT(YEAR FROM order_date) = 2000
)
PIVOT
(SUM(order_total) for order_mode in ('direct' as store, 'online' as internet))
order by 1
/

As you can see I gave aliases to the values I used in the pivot (e.g. store and internet) and then used those in the outer query to do some fun stuff. Notably add the two together to get a total value for the month. The result is something like this:

final_pivot_plus_total_results.JPG

Interestinly enough, I just compared this example with the examples on the OWB blog (here) and it seems that OWB has reversed the pivot and unpivot operators when compared with the SQL commands in Oracle... Ah well, minor issue, just make note of it...

Comments:

Many thanks for the simple example to get the point across. All the more reason now for me to head down and read on 11g enhancements.

Posted by SANTOSH LOKE on March 05, 2009 at 12:09 AM PST #

Extract was introduced in 9i, I believe. I am using it in 9.2 right now.

Posted by Eugene on March 05, 2009 at 12:59 AM PST #

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today