Need to Split Date Column into Multiple Columns by year

Question

I'm trying to split a date column into multiple columns by year. If I could achieve a roll-up subtotal at the end that would be awesome.

I'm thinking the best way to do it, is using a .NET datalist control, but I'm open to suggestions.

Example data:

DATE     | SALES
1/1/2009 | 1234.56
2/1/2009 | 4567.89
3/1/2009 | 7890.12
...
1/1/2010 | 3456.78
...
1/1/2011 | 8901.23

Desired output:

       2009     2010     2011
Jan    1234.56  3456.78  8901.23
Feb    4567.89  ...

Thanks in advance!

Answer 1

Why not just query it as a pivot table, then display it using any front end you consider easiest and best suited.

http://msdn.microsoft.com/en-us/library/ms177410.aspx