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

You want a table with 9 million columns? Sounds like that will blow away the design specifications of most DB programs.

Mark Graham wrote:

> 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