Pivoting large result sets

Heres the scenario:
We have a database that stores values for different characteristics taken at different times. The data is stored in the following format:

POSTED_UTS CHAR_ID RESULT_VALUE_FLOAT 2005-08-09 14:30:03.907 1859 1.08 2005-08-09 14:30:03.907 1860 1.07 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.937 1859 0.01 2005-08-09 14:30:03.937 1860 0.01 2005-08-09 14:30:03.937 1861 0.01 2005-08-09 14:30:03.953 1756 0.01 2005-08-09 14:30:03.953 1757 0.01 2005-08-09 14:30:03.953 1859 0.01 2005-08-09 14:30:03.953 1859 0.01

The result set for a two hour time span returns >41,000 rows, the result set for a one week time span returns >2.9 million rows. We have multiple data sources, each with its own set of characteristics, each source takes readings at different times and then stores those readings in the above schema. The problem we are running into is, our end users want the data in the following format:

Posted UTS Char 1 Char 2 Char 3 Char 4 . . . Char <n> 2005-08-09 14:00:00.000 2.3 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:30.000 2.3 3.4 5 66.8875 . . . <value n> 2005-08-09 14:00:00.000 NULL 3.4 NULL NULL . . . <value n> 2005-08-09 14:00:00.000 5.6 NULL NULL NULL . . . <value n>

Needless to say, when we pivot on a two hour block, it does take all that long (just over a minute), but when we try to pivot over a one week block, it takes considerably longer, much longer than anyone likes.

Is there a better way of doing this? Would storing the data in the same schema as we want to report in be wiser?

System Information: 2x XEON (Hyperthreaded to 4) 3Ghz
3GB ram
SQL Server 2005
Reporting Services 2005

Thanks for any help.

Wayne E. Pfeffer

The only fast solution I can think of is to use an ssas cube to pivot this

Philippe