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

Sounds like you had fun...

Mark Graham wrote in message ...
>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 Sun Jan 04 1998 - 00:00:00 CST