Posted by Simon Hayes on 09/29/05 16:14

A crosstab-style query would be the usual SQL solution, dynamically
generated if the number of years is not known in advance - Google for
"dynamic crosstab query", "pivot query" and "reverse pivot query" for
examples. How well that performs would depend on your data volume,
indexes etc., so it's difficult to give a specific answer.

You should also consider doing the crosstab on the client side, where
it may be easier to write and maintain than using dynamic SQL (which
can also be a security issue). Or use a third-party reporting tool to
do this for you, rather than write your own code - MSSQL Reporting
Services is free, for example.

Simon

[Back to original message]