Tuesday, October 9, 2012

Three Ways To Transpose Rows Into Columns in Oracle SQL

How to transpose rows to columnsHave you ever been in a situation where you as a developer knows that your data is stored in your table rows, but your customer don’t care…wanting to present the data as columns?In this tutorial I will discuss three different variants on how to transpose table rows into columns. By using SUM + DECODE, the new Oracle 11g PIVOT operator, and by using WITH + sub SELECTs.

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:
1SQL> SELECT  o.order_mode
2  2          ,o.order_status
3  3          ,SUM(o.order_total) order_total
4  4  FROM   orders o
5  5  GROUP BY o.order_mode, o.order_status
6  6  ORDER BY o.order_mode
7  7          ,o.order_status
8  8  /
9
10ORDER_MODE ORDER_STATUS ORDER_TOTAL
11---------- ------------ -----------
12direct                0    163131.3
13direct                1    227569.5
14direct                2    166169.5
15direct                3    206659.4
16direct                4     56352.5
17direct                5    172586.2
18direct                6      115968
19direct                7     33617.1
20direct                8    545300.5
21direct                9    205674.2
22direct               10       10601
23online                0     25976.7
24online                2    103834.4
25online                3     56381.7
26online                4    700068.1
27online                5    183261.2
28online                6     90411.8
29online                8    322192.5
30online                9     57062.4
31online               10    225236.7
32
3320 rows selected
What we want to do is to present the data like this:
1ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
2---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
4ONLINE        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.
1SQL> SELECT  UPPER(o.order_mode) order_mode
2  2         ,SUM(DECODE(o.order_status, 0, o.order_total)) stat_0
3  3         ,SUM(DECODE(o.order_status, 1, o.order_total)) stat_1
4  4         ,SUM(DECODE(o.order_status, 2, o.order_total)) stat_2
5  5         ,SUM(DECODE(o.order_status, 3, o.order_total)) stat_3
6  6         ,SUM(DECODE(o.order_status, 4, o.order_total)) stat_4
7  7         ,SUM(DECODE(o.order_status, 5, o.order_total)) stat_5
8  8         ,SUM(DECODE(o.order_status, 6, o.order_total)) stat_6
9  9         ,SUM(DECODE(o.order_status, 7, o.order_total)) stat_7
10 10         ,SUM(DECODE(o.order_status, 8, o.order_total)) stat_8
11 11         ,SUM(DECODE(o.order_status, 9, o.order_total)) stat_9
12 12         ,SUM(DECODE(o.order_status, 10, o.order_total)) stat_10
13 13  FROM   orders o
14 14  GROUP BY o.order_mode
15 15  ORDER BY 1
16 16  /
17
18ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
19---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
20DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
21ONLINE        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:
1SQL> col scenario format a10
2SQL> SELECT  r.scenario
3  2          ,r.currency
4  3          ,r.rate
5  4  FROM   rates r
6  5  /
7
8  SCENARIO CURRENCY       RATE
9---------- -------- ----------
10         1 USD            1.00
11         1 CAD            1.45
12         1 MYR            3.34
13         1 NOK            6.08
14         2 USD            1.00
15         2 CAD            1.47
16         2 MYR            3.47
17         2 NOK            6.12
18         3 USD            1.00
19         3 CAD            1.51
20         3 MYR            2.98
21         3 NOK            5.96
22
2312 rows selected
To show a nice crosstab version of our data, the query below would do the trick:
1SQL> SELECT  x.scenario
2  2         ,MAX(DECODE(x.currency, 'CAD', x.rate)) CAD_rate
3  3         ,MAX(DECODE(x.currency, 'MYR', x.rate)) MYR_rate
4  4         ,MAX(DECODE(x.currency, 'NOK', x.rate)) NOK_rate
5  5         ,MAX(DECODE(x.currency, 'USD', x.rate)) USD_rate
6  6  FROM   (
7  7              SELECT r.scenario
8  8                      ,r.currency
9  9                      ,r.rate
10 10              FROM   rates r
11 11         ) x
12 12  GROUP BY x.scenario
13 13  /
14
15  SCENARIO   CAD_RATE   MYR_RATE   NOK_RATE   USD_RATE
16---------- ---------- ---------- ---------- ----------
17         1       1.45       3.34       6.08          1
18         2       1.47       3.47       6.12          1
19         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.
1SQL> SELECT  UPPER(order_mode)
2  2         ,stat_0
3  3         ,stat_1
4  4         ,stat_2
5  5         ,stat_3
6  6         ,stat_4
7  7         ,stat_5
8  8         ,stat_6
9  9         ,stat_7
10 10         ,stat_8
11 11         ,stat_9
12 12         ,stat_10
13 13  FROM   (
14 14              SELECT o.order_mode
15 15                      ,o.order_status
16 16                      ,SUM(o.order_total) order_total
17 17              FROM   orders o
18 18              GROUP BY o.order_mode, o.order_status
19 19         )
20 20  PIVOT  (
21 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 22  )
23 23  /
24
25UPPER(ORDER_MODE)     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
26----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
27DIRECT              163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
28ONLINE               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.
1SQL> WITH main_query AS (
2  2                          SELECT o.order_mode
3  3                                  ,o.order_status
4  4                                  ,SUM(o.order_total) order_total
5  5                          FROM   orders o
6  6                          GROUP BY o.order_mode, o.order_status
7  7  )
8  8  SELECT UPPER(x.order_mode) order_mode
9  9         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 0 ) stat_0
10 10         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 1 ) stat_1
11 11         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 2 ) stat_2
12 12         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 3 ) stat_3
13 13         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 4 ) stat_4
14 14         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 5 ) stat_5
15 15         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 6 ) stat_6
16 16         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 7 ) stat_7
17 17         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 8 ) stat_8
18 18         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 9 ) stat_9
19 19         ,(SELECT y.order_total FROM main_query y WHERE y.order_mode = x.order_mode AND y.order_status = 10 ) stat_10
20 20  FROM   (
21 21              SELECT DISTINCT m.order_mode
22 22              FROM   main_query m
23 23         ) x
24 24  /
25
26ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10
27---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
28DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601
29ONLINE        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.

Posted by at

1 comment :

  1. James ZicrovNovember 21, 2020 at 12:57 AM

    I feel Informatica is the best and most useful aspect through which complex IT problems get solved very easily.

    Informatica Read Soap Api

    ReplyDelete

Subscribe to: Post Comments ( Atom )