Group by Month

If I have a date column and want to return a columns that sum by each month of the year, what is the best way to do that?

Example

Date Amount

1/3/2007 10

1/7/2007 15

3/4/2007 8

3/21/2007 19

5/33/2007 12

9/6/2007 5

12/8/20007 4

12/12/2007 10

Return:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Amount 25 0 27 0 12 0 0 0 5 0 0 14

The most convenient way is to use a PIVOT based on the DATEPART of the date using 'MM' -- the month part -- as the target datepart. Another alternative would involve the use of SUM and CASE over 12 different columns again based on a date part.

( My overview sounds like mumbo jumbo to me, too. Can somebody pick me up? )

|||

Here are a couple different approaches, some useful for SQL 2000/2005, and some only for SQL 2005.

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||Thanks, guys. That works for me.