按月份显示数据，按月区分

Question

例如ID      Uname         downtime     
1       小米        2011-01-12 2       小黑        2011-01-173        黎明       2011-02-24        小黑       2011-03-155        王菲       2011-07-3要得到是结果是：
用户   1月下载次数    2月下载次数    3月下载次数  ....12月下载次数 小米    1                   0             0                  0小黑    1                   0              1                  0

Answer 1

create table tb(ID int,Uname nvarchar(10),downtime datetime)
insert into tb select 1,'小米','2011-01-12'
insert into tb select 2,'小黑','2011-01-17'
insert into tb select 3,'黎明','2011-02-2'
insert into tb select 4,'小黑','2011-03-15'
insert into tb select 5,'王菲','2011-07-3'
go
declare @s nvarchar(max)
select @s=isnull(@s+',','')+'['+ dt +']' from(
select convert(varchar(7),dateadd(m,number,'2011-01-01'),120)dt from master..spt_values where type='p' and number<12
)t
exec('select Uname,'+@s+'from (select Uname,convert(varchar(7),downtime,120)dt from tb)t pivot (count(dt) for dt in('+@s+'))b')
/*
Uname      2011-01     2011-02     2011-03     2011-04     2011-05     2011-06     2011-07     2011-08     2011-09     2011-10     2011-11     2011-12
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
黎明         0           1           0           0           0           0           0           0           0           0           0           0
王菲         0           0           0           0           0           0           1           0           0           0           0           0
小黑         1           0           1           0           0           0           0           0           0           0           0           0
小米         1           0           0           0           0           0           0           0           0           0           0           0(4 行受影响)
*/
go
drop table tb