--测试数据 create table tb1(name varchar(10),qty int,date datetime) insert tb1 select 'n1','1','2003/12/01' union all select 'n2','4','2004/03/14' union all select 'n3','1','2004/02/02' union all select 'n2','7','2003/11/02' union all select 'n1','3','2004/07/03' union all select 'n3','6','2004/06/14' union all select 'n2','8','2004/06/13' union all select 'n2','5','2004/05/14' go--查询 declare @s varchar(8000),@i int select @s='',@i=0 set language english while @i>-4 select @s=',['+datename(month,dateadd(month,@i,getdate())) +']=sum(case datename(month,date) when ''' +datename(month,dateadd(month,@i,getdate())) +''' then qty else 0 end)'+@s ,@i=@i-1 exec('select name'+@s+' from tb1 where datediff(month,[date],getdate()) between 0 and 3 group by name,year(date)') set language 简体中文 go--删除测试 drop table tb1/*--测试结果name April May June July ---------- ----------- ----------- ----------- ----------- n1 0 0 0 3 n2 0 5 8 0 n3 0 0 6 0 --*/
你的意思是先建一个月,然后往里面填数据?
create table tb1(name varchar(10),qty int,date datetime)
insert tb1 select 'n1','1','2003/12/01'
union all select 'n2','4','2004/03/14'
union all select 'n3','1','2004/02/02'
union all select 'n2','7','2003/11/02'
union all select 'n1','3','2004/07/03'
union all select 'n3','6','2004/06/14'
union all select 'n2','8','2004/06/13'
union all select 'n2','5','2004/05/14'
go--查询
declare @s varchar(8000),@i int
select @s='',@i=0
set language english
while @i>-4
select @s=',['+datename(month,dateadd(month,@i,getdate()))
+']=sum(case datename(month,date) when '''
+datename(month,dateadd(month,@i,getdate()))
+''' then qty else 0 end)'+@s
,@i=@i-1
exec('select name'+@s+'
from tb1
where datediff(month,[date],getdate()) between 0 and 3
group by name,year(date)')
set language 简体中文
go--删除测试
drop table tb1/*--测试结果name April May June July
---------- ----------- ----------- ----------- -----------
n1 0 0 0 3
n2 0 5 8 0
n3 0 0 6 0
--*/