select 姓名,
[6月]=sum(case when datediff(month,'2007-06-01','日期')=0 then 金额 else 0 end),
[7月]=sum(case when datediff(month,'2007-07-01','日期')=0 then 金额 else 0 end),
from [Table] group by 姓名
[6月]=sum(case when datediff(month,'2007-06-01','日期')=0 then 金额 else 0 end),
[7月]=sum(case when datediff(month,'2007-07-01','日期')=0 then 金额 else 0 end),
from [Table] group by 姓名
insert into #t
select '张三',500,'2007-7-11'
union all select '张三',100,'2007-6-5'
union all select '张三',200,'2007-6-15'
union all select '陈五',100,'2007-7-4'
union all select '陈五',400,'2007-7-5'
union all select '陈五',600,'2007-6-21'select * from #tdeclare @s varchar(8000)
set @s=''
select @s=@s+',sum(case when convert(char(7),日期,120)='''+日期+''' then 金额 else 0 end) as '''+right(日期,2)+'月'''
from (select distinct convert(char(7),日期,120) as 日期 from #t)t
order by 日期
exec('select 姓名'+@s+' from #t group by 姓名')
/*
姓名 06月 07月
---------- ----------- -----------
陈五 600 500
张三 300 500
*/
select 姓名,
[6月]=sum(case when datediff(month,'2007-06-01',日期)=0 then 金额 else 0 end),
[7月]=sum(case when datediff(month,'2007-07-01',日期)=0 then 金额 else 0 end)
from [Table] group by 姓名
SELECT @sql = 'select 姓名'
select @sql = @sql + ',sum(case month(日期) when '''+month(日期)+''' then 金额 else 0 end) as '''+month(日期)+'月''' from (select distinct month(日期) from 表) as a
select @sql = @sql + ' from 表 group by 姓名'
exec(@sql)
insert into #
select '张三',500,'2007-7-11'
union all select '张三',100,'2007-6-5'
union all select '张三',200,'2007-6-15'
union all select '陈五',100,'2007-7-4'
union all select '陈五',400,'2007-7-5'
union all select '陈五',600,'2007-6-21'
select 姓名,max(case when 月份=6 then 金额 end) as 六月,
max(case when 月份=7 then 金额 end)as 七月
from (
select 姓名,datepart(mm,日期) as 月份 , sum(金额) as 金额 from #
group by 姓名,datepart(mm,日期)
) a
group by 姓名