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

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 Mon Jan 05 1998 - 00:00:00 CST