Dave , run time is 1 second, you I believe, are the man.

Thanks for all the help and Merry Christmas

W

Morning All,

So Dave's rebuild the statistics suggestion got the code to run in 20 seconds approx (massive gain there) so thanks very much Dave hats off to you, I'd...

Thanks Dave I've set the stats to update on the entire database tonight and I'll let you know how I get on on Monday.

W

Thanks Lynn, I've posted them above and got rid of the xml

W

Sql plans now attached in post above.

Joe please find the good and bad sql plans attached just delete the .txt which I added to ensure they'd upload.

Thanks

Will

Is XML format OK Joe?

Well played, I enjoyed that. At least a chocolate ice cream always expectation manages my kid to a satisfied customer.

Thanks for all the input guys. I'll try and answer some of the points you raised, in no order

1, Theres no requirement for these to be hot swapped on failure...

Hey Dale ,

Is this multi-selects in Reporting services ?

Will

Yes Jeff , 500 columns,, that was my initial reaction too.

1 column is an Id column and the other 500 are financial risk values for the last 2 years generated by...

Thanks David ,I was just about to point up that its a row or multiple rows and you beat me to it.

What technique would you use to turn the rows...

Michael

I feel you pain, I tried to get this to work and failed with much frustration.

What does the Macro do ?  Can you duplicate the process in SQL...

Michael,

What I do in a smiliar situation is output data to a flat file Out.txt and then rename the txt using either a script or a file system task and it works...

Michael ,

Its like you've seen the file already, yep I receive it as an overnight tab delimited text file.

My transpose stored proc does exactly what Andy suggested (thanks Andy)...