数据表Order.dbo结构：
OrderNo varchar(10), ---单号
GetDate Varchar(10), ---日期
Sales Varchar(10), ---员
ItemNo Varchar(20), ---产品号
Amount Smallint ---金额

数据记录如下：
001 2004-01-01 ABC A001 1000
001 2004-01-15 QQQ A001 5000
002 2004-02-01 ABC A001 2000
003 2004-03-01 ABC A001 3000
004 2004-06-01 ABC A001 6000
......

要求输入欲查询的年度条件如：2004，得到按月份统计的如下结果：
Slaes 200401 200402 200403 200404 200405 200406 200407 200408 200409 200410 200411
----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
ABC 1000 2000 3000 6000
QQQ 5000

第一种答案：

declare @sql varchar(8000)
set @sql = 'select Sales'
select @sql = @sql + ',sum(case left(GetDate,7) when '''+left(GetDate,7)+''' then Amount end) ['+ replace(left(GetDate,7),'-','')+']'

from (select distinct left(GetDate,7) as GetDate from test) as a
select @sql = @sql+' from test group by Sales'
print @sql
exec(@sql)

第二种答案：

SQL语句:
select
sales as '业务员',
sum(case when month(getdatetime)=1 then amount end) as '1月份总额',
sum(case when month(getdatetime)=2 then amount end) as '2月份总额',
sum(case when month(getdatetime)=3 then amount end) as '3月份总额',
sum(case when month(getdatetime)=4 then amount end) as '4月份总额',
sum(case when month(getdatetime)=5 then amount end) as '5月份总额',
sum(case when month(getdatetime)=6 then amount end) as '6月份总额',
sum(case when month(getdatetime)=7 then amount end) as '7月份总额',
sum(case when month(getdatetime)=8 then amount end) as '8月份总额',
sum(case when month(getdatetime)=9 then amount end) as '9月份总额',
sum(case when month(getdatetime)=10 then amount end) as '10月份总额',
sum(case when month(getdatetime)=11 then amount end) as '11月份总额',
sum(case when month(getdatetime)=12 then amount end) as '12月份总额'
from you_table group by sales
--********************************************************************************
--存储过程
create procedure sp_ordertable
@Year int
as
select
sales as '业务员',
sum(case when month(getdatetime)=1 then amount end) as '1月份总额',
sum(case when month(getdatetime)=2 then amount end) as '2月份总额',
sum(case when month(getdatetime)=3 then amount end) as '3月份总额',
sum(case when month(getdatetime)=4 then amount end) as '4月份总额',
sum(case when month(getdatetime)=5 then amount end) as '5月份总额',
sum(case when month(getdatetime)=6 then amount end) as '6月份总额',
sum(case when month(getdatetime)=7 then amount end) as '7月份总额',
sum(case when month(getdatetime)=8 then amount end) as '8月份总额',
sum(case when month(getdatetime)=9 then amount end) as '9月份总额',
sum(case when month(getdatetime)=10 then amount end) as '10月份总额',
sum(case when month(getdatetime)=11 then amount end) as '11月份总额',
sum(case when month(getdatetime)=12 then amount end) as '12月份总额'
from lpy_ordertable where year(getdatetime)=@year group by sales

exec sp_ordertable 2004

业务员 1月份总额 2月份总额 3月份总额 4月份总额 5月份总额 6月份总额 7月份总额 8月份总额 9月份总额 10月份总额 11月份总额 12月份总额
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
abc 1000 2000 3000 NULL NULL 6000 NULL NULL NULL NULL NULL NULL
qqq 5000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

（所影响的行数为 2 行）