Introduction
According to Wikipedia®, one definition of the word “transpose” is:A matrix obtained from a given matrix by interchanging each row and the corresponding column.There are many technical variants on what transposing really is, but in our case it is simply to present row values as columns. This is very helpful if you i.e. want to present data in a crosstab, or matrix. An example can i.e. be that you have your order table where each row has an order date, and you want to present the sales per month (extracted from the order date) in columns. A very common situation in Data Warehouse reporting.
This functionality has existed a long time in Excel (transpose when you use “Paste Special..“), reporting tools like Web Intelligence (Business Objects), etc.
In Oracle it has been possible to mimic this functionality for a long time…but until version 11g, it had not been a thought through functionality.
We are going to look at three of the most common ways to do row to column transposing.
Examples
For our three examples we are going to use the following query as a base:SQL> SELECT o.order_mode
2 ,o.order_status
3 ,SUM(o.order_total) order_total
4 FROM orders o
5 GROUP BY o.order_mode, o.order_status
6 ORDER BY o.order_mode
7 ,o.order_status
8 /
ORDER_MODE ORDER_STATUS ORDER_TOTAL
---------- ------------ -----------
direct 0 163131.3
direct 1 227569.5
direct 2 166169.5
direct 3 206659.4
direct 4 56352.5
direct 5 172586.2
direct 6 115968
direct 7 33617.1
direct 8 545300.5
direct 9 205674.2
direct 10 10601
online 0 25976.7
online 2 103834.4
online 3 56381.7
online 4 700068.1
online 5 183261.2
online 6 90411.8
online 8 322192.5
online 9 57062.4
online 10 225236.7
20 rows selected
What we want to do is to present the data like this:
ORDER_MODE STAT_0 STAT_1 STAT_2 STAT_3 STAT_4 STAT_5 STAT_6 STAT_7 STAT_8 STAT_9 STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT 163131.3 227569.5 166169.5 206659.4 56352.5 172586.2 115968 33617.1 545300.5 205674.2 10601
ONLINE 25976.7 103834.4 56381.7 700068.1 183261.2 90411.8 322192.5 57062.4 225236.7
Ok, let’s get down to some business…
Example # 1a: Transpose rows into columns using SUM and DECODE to summarize data
Before version 11g of Oracle this method of transposing data was probably the most proficient. It is actually the exact query that generated the data result shown above.SQL> SELECT UPPER(o.order_mode) order_mode
2 ,SUM(DECODE(o.order_status, 0, o.order_total)) stat_0
3 ,SUM(DECODE(o.order_status, 1, o.order_total)) stat_1
4 ,SUM(DECODE(o.order_status, 2, o.order_total)) stat_2
5 ,SUM(DECODE(o.order_status, 3, o.order_total)) stat_3
6 ,SUM(DECODE(o.order_status, 4, o.order_total)) stat_4
7 ,SUM(DECODE(o.order_status, 5, o.order_total)) stat_5
8 ,SUM(DECODE(o.order_status, 6, o.order_total)) stat_6
9 ,SUM(DECODE(o.order_status, 7, o.order_total)) stat_7
10 ,SUM(DECODE(o.order_status, 8, o.order_total)) stat_8
11 ,SUM(DECODE(o.order_status, 9, o.order_total)) stat_9
12 ,SUM(DECODE(o.order_status, 10, o.order_total)) stat_10
13 FROM orders o
14 GROUP BY o.order_mode
15 ORDER BY 1
16 /
ORDER_MODE STAT_0 STAT_1 STAT_2 STAT_3 STAT_4 STAT_5 STAT_6 STAT_7 STAT_8 STAT_9 STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT 163131.3 227569.5 166169.5 206659.4 56352.5 172586.2 115968 33617.1 545300.5 205674.2 10601
ONLINE 25976.7 103834.4 56381.7 700068.1 183261.2 90411.8 322192.5 57062.4 225236.7
As you probably see, for each of the eleven possible order_status values, there is a “stat_x” line. If we had twelve values…well…then it would have been twelve “stat_x” fields. This is actually one of the situations where I find it easier to use a DECODE function, rather than CASE. If you want to know about the difference between CASE, and DECODE – please take a look at the post named “Oracle DECODE and CASE: What is the difference“.
Example # 1b: Transpose rows into columns using MAX and DECODE – for non-summarized data
I want to show another variant of this (above) query as well.Let us pretend we are going to help our MLM (Multi Level Marketing) company with calculating commission for our distributors, and we want to calculate everything using three different rate scenarios to be able to measure the commission impact against each other. And, we just want to show all the rates in a nice way, grouped by each scenario.
Ok, this is the data we start out with:
SQL> col scenario format a10
SQL> SELECT r.scenario
2 ,r.currency
3 ,r.rate
4 FROM rates r
5 /
SCENARIO CURRENCY RATE
---------- -------- ----------
1 USD 1.00
1 CAD 1.45
1 MYR 3.34
1 NOK 6.08
2 USD 1.00
2 CAD 1.47
2 MYR 3.47
2 NOK 6.12
3 USD 1.00
3 CAD 1.51
3 MYR 2.98
3 NOK 5.96
12 rows selected
To show a nice crosstab version of our data, the query below would do the trick:
SQL> SELECT x.scenario
2 ,MAX(DECODE(x.currency, 'CAD', x.rate)) CAD_rate
3 ,MAX(DECODE(x.currency, 'MYR', x.rate)) MYR_rate
4 ,MAX(DECODE(x.currency, 'NOK', x.rate)) NOK_rate
5 ,MAX(DECODE(x.currency, 'USD', x.rate)) USD_rate
6 FROM (
7 SELECT r.scenario
8 ,r.currency
9 ,r.rate
10 FROM rates r
11 ) x
12 GROUP BY x.scenario
13 /
SCENARIO CAD_RATE MYR_RATE NOK_RATE USD_RATE
---------- ---------- ---------- ---------- ----------
1 1.45 3.34 6.08 1
2 1.47 3.47 6.12 1
3 1.51 2.98 5.96 1
BTW – The rates shown are totally random…
Example # 2: Transpose rows into columns using the Oracle PIVOT operator
The PIVOT and the UNPIVOT operators were introduced in Oracle version 11g. The following query will give the same result as the query above, just by using the PIVOT operator.SQL> SELECT UPPER(order_mode)
2 ,stat_0
3 ,stat_1
4 ,stat_2
5 ,stat_3
6 ,stat_4
7 ,stat_5
8 ,stat_6
9 ,stat_7
10 ,stat_8
11 ,stat_9
12 ,stat_10
13 FROM (
14 SELECT o.order_mode
15 ,o.order_status
16 ,SUM(o.order_total) order_total
17 FROM orders o
18 GROUP BY o.order_mode, o.order_status
19 )
20 PIVOT (
21 SUM(order_total) FOR order_status IN (0 stat_0, 1 stat_1, 2 stat_2, 3 stat_3, 4 stat_4, 5 stat_5, 6 stat_6, 7 stat_7, 8 stat_8, 9 stat_9, 10 stat_10)
22 )
23 /
UPPER(ORDER_MODE) STAT_0 STAT_1 STAT_2 STAT_3 STAT_4 STAT_5 STAT_6 STAT_7 STAT_8 STAT_9 STAT_10
----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT 163131.3 227569.5 166169.5 206659.4 56352.5 172586.2 115968 33617.1 545300.5 205674.2 10601
ONLINE 25976.7 103834.4 56381.7 700068.1 183261.2 90411.8 322192.5 57062.4 225236.7
Example # 3: Transpose rows into columns using WITH and sub SELECTS
Ok, in this example we are going to put our main query inside a WITH statement, and then query the WITH materialized resultset multiple times. Using WITH, the main resultset wil not be recomputed multiple times.SQL> WITH main_query AS (
2 SELECT o.order_mode
3 ,o.order_status
4 ,SUM(o.order_total) order_total
5 FROM orders o
6 GROUP BY o.order_mode, o.order_status
7 )
8 SELECT UPPER(x.order_mode) order_mode
9 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 0 ) stat_0
10 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 1 ) stat_1
11 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 2 ) stat_2
12 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 3 ) stat_3
13 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 4 ) stat_4
14 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 5 ) stat_5
15 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 6 ) stat_6
16 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 7 ) stat_7
17 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 8 ) stat_8
18 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 9 ) stat_9
19 ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 10 ) stat_10
20 FROM (
21 SELECT DISTINCT m.order_mode
22 FROM main_query m
23 ) x
24 /
ORDER_MODE STAT_0 STAT_1 STAT_2 STAT_3 STAT_4 STAT_5 STAT_6 STAT_7 STAT_8 STAT_9 STAT_10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DIRECT 163131.3 227569.5 166169.5 206659.4 56352.5 172586.2 115968 33617.1 545300.5 205674.2 10601
ONLINE 25976.7 103834.4 56381.7 700068.1 183261.2 90411.8 322192.5 57062.4 225236.7
Well, there you have it. Three different ways to get the same data.
Other ways to solve the problem
Although only three different methods are presented here, there are actually other ways to transpose the data from rows to columns. One of them is to use a WITH statement, and then multiple UNIONs with a SUM of all columns at the end. I will let it be up to you to try this one out. If you really would like to know how to do this, leave a comment below, and I will give you a sample. It is a more cumbersome (reason for not initially showing a sample) way to do the transposing this way.Yet another possibility, after my understanding, is to use XML in your query. I will give a sample on our PL/SQL site for this one.
Conclusion
As you can see, there are a few ways to to transpose rows into columns in Oracle. If you have a different..or even better way to do this…we will all love to hear about it. The comment field is made just for you.
BTW – If you wonder about the data used in the examples, they are from the “OE” sample schema that comes with Oracle. It is normally locked and expired by default.
Hope this help you out in your work,
Source: http://oracletuts.net/tutorials/three-ways-to-transpose-rows-into-columns-in-oracle-sql/
Không có nhận xét nào:
Đăng nhận xét