? declare @mouth int
? declare @start nvarchar(20)
? declare @end nvarchar(20)
? declare @sql nvarchar(3000)

? set @mouth=1
? while @mouth<=12
? begin
??
?? if @mouth=12
?? begin
? set @start='2011-'+ltrim(rtrim(STR(@mouth)))+'-1'
? set @end='2012-'+ltrim(rtrim(STR(1)))+'-1'
?? end
?? else
?? begin
? set @start='2011-'+ltrim(rtrim(STR(@mouth)))+'-1'
? set @end='2011-'+ltrim(rtrim(STR(@mouth+1)))+'-1'
?? end
??
?? if @mouth=1
?? begin
?set @sql=N'select '+ltrim(rtrim(cast(@mouth as CHAR)))+' as ''月份'',count(1) as ''單數'',SUM(amount) as ''金額'' FROM [szjdw].[dbo].[mf_order] where cop_id=''DGJDW'' and createdt>'''+@start+''' and createdt< '''+@end+''''
?? end
?? else
?? begin
?set @sql=@sql+' union '+'select '+ltrim(rtrim(cast(@mouth as CHAR)))+' as ''月份'',count(1) as ''單數'',SUM(amount) as ''金額'' FROM [szjdw].[dbo].[mf_order] where cop_id=''DGJDW'' and createdt>'''+@start+''' and createdt< '''+@end+''''
?? end
?? set @mouth=@mouth+1
? end
? select @sql
? exec(@sql)
? go

?

按照月份統計的匯總