For some reason I can't get this to pivot the results - It runs but just lists the data in a column. I'll play with it and see where it...

Thanks Chris but it's a dynamic query. There could be 10 columns (after the pivot) or 100 so I can't hardcode fields into the script.

Had thought about just creating...

Ok been playing with this a lot this morning and I think I've fixed the issue with the row_number. Added a where templateid = xxx for the tbl_forms. Now at...

Chris/Thomas

Thanks for the replies.

Chris - Understand your solution however the row id in it comes up in the 30K range (even if I select from 0 to 50. It's...

Done a lot more digging and found that the xml path is taking 84% of the total query time, where the dynamic query is only 14%.

Anyone got any ideas on...

Twin.Devil

thanks. I checked the UDF and it's not impacting it (much). I took a look at the link you gave me on cross tabs - will need to get a...