Pivoting a large table, like Cash Flows, in TSQL

I often have to pivot large amounts of data, but the Pivot option in TSQL can be VERY limited especially when there is a large amount of data. The TSQL query below will create a sample Cash Flow table then pivot it on Date, but this can be modified to pivot the data in any number of ways.



The output will look similar to this:

AccountID	[2010-1]	[2010-2]	[2010-3]	[2010-4]	[2010-5]	[2010-6]
123	117.89	515.86	205.81	516.29	387.64	30.84
390	363.94	992.20	197.64	516.29	248.20	172.05
687	129.86	562.17	839.67	790.28	790.28	898.51

I've used a query similar to this for pivoting LARGE cash flow tables for reporting, and given you have no objections to using Dynamic SQL this is a great way to pivot large amounts of data.

Have fun and please post comments and suggestions.