Here is a solution to transpose a table efficiently (columns to rows)

From: Mark Graham <mlgraham_at_bellsouth.net>
Date: 1998/01/03
Message-ID: <Zsgr.2060$VK3.2392705@news1.atl.bellsouth.net>#1/1

Below is an example of how to transpose a table efficiently using a technique I saw in a database magazine about 4 years ago. I was hoping someone out there would know of an efficient routine to transpose a table from rows to columns (opposite direction to that shown below).

Problem:
Suppose you have the following table in your database:

WELL_ID J F M
----------- ----------- ----------- -----------

1           5           2           6
2           10          2           6
3           15          2           6

The well_id is the primary key and the columns "J","F","M" are the months which contain data that was obtained during these months.

You want to transpose the table above into the following:

WELL_ID amount month
----------- ----------- -----------

1           5           1
1           2           2
1           6           3
2           10          1
2           2           2
2           6           3
3           15          1
3           2           2
3           6           3

Solution:
First you must create the following table:

MONTHS

1
2
3

Next you run the following SQL and the result will be the transposed values shown above.

select      w.WELL_ID,
                amount =isnull(0/(m.MONTHS-1),w.J)+
                             isnull(0/(m.MONTHS-2),w.F)+
                             isnull(0/(m.MONTHS-3),w.M),
                month  =1*(1-abs(sign(m.MONTHS-1)))+
                             2*(1-abs(sign(m.MONTHS-2)))+
                             3*(1-abs(sign(m.MONTHS-3)))

from WELL w, MONTHS m

The solution I have shown uses the "isnull" function which is SQL-Server and Sybase specific. You can use a decode as a substitute in in Oracle. Please let me know if anyone has an efficient way of transposing the table in the opposite direction to the example above. I need to be able to convert a 9 million row table from rows to columns.

Mark
mlgraham_at_bellsouth.net Received on Sat Jan 03 1998 - 00:00:00 CST