Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Here is a solution to transpose a table efficiently (columns to rows)
Geeez. We got it the first four times Mark... Go easy on that send button.
Mark Graham wrote:
> As in the example the table would have the just 3 additional columns, one
> for each month. Currently there is just one column which contains a month
> value.
>
> The table was originally in Oracle. It is a time series and was going to be
> used with the time series datatype Oracle offers in version 8. During
> development we converted to SQL-Server and the table needs to be transposed
> in order to get some performance benefits for date sensitive reporting. The
> original history table is still required as well.
>
> Incidentally I can transpose the table in Oracle 7.3.3 using a decode
> function in 90 seconds (4 pentium pros + 12 drives). Unfortunately
> SQL-Server forces me to try to use standard SQL because the case statement
> just doesn't cut it. I know there was a solution developed by a few
> professors I have seen awhile back. The problem is that I cannot remember
> it and was hoping someone else had developed an efficient algorithm or has
> seen an example. The alternative I am looking at is to write a terribly
> innefficient SQL loop.
>
> Mark
Received on Thu Jan 08 1998 - 00:00:00 CST