Friday, April 01, 2011

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.

No comments: