测试数据：
create table #table(id int,y_month char(7),num int)
insert into #table values(0,'2009-12',519),
(0,'2009-2',463),
(0,'2009-9',723),
(0,'2010-1',199),
(0,'2010-8',371),
(0,'2011-1',278),
(0,'2011-12',437),
(0,'2011-2',441),
(1,'2009-1',370),
(1,'2009-9',230),
(1,'2010-1',247),
(1,'2011-11',821),
(2,'2009-1',836),
(2,'2010-8',416),
(2,'2011-9',799);
问题：
统计每年的每个月的num数值？
结果形式：(id=2的部分) 要求：对同一个id，月份不能重复。
id 月份 [2009] [2010] [2011]
2 1 836 NULL NULL
2 2 NULL NULL NULL
2 3 NULL NULL NULL
. . . . .
. . . . .
. . . . .
2 8 NULL 416 NULL
2 9 NULL NULL 799
2 10 NULL NULL NULL
. . . . .
------解决方案--------------------
稍微调整了一下，试试看
- SQL code
declare @table table(id int,y_month datetime,num int)insert into @table select 0,'2009-12-1',519 union allselect 0,'2009-2-1',463 union allselect 0,'2009-9-1',723 union allselect 0,'2010-1-1',199 union allselect 0,'2010-8-1',371 union allselect 0,'2011-1-1',278 union allselect 0,'2011-12-1',437 union allselect 0,'2011-2-1',441 union allselect 1,'2009-1-1',370 union allselect 1,'2009-9-1',230 union allselect 1,'2010-1-1',247 union allselect 1,'2011-11-1',821 union allselect 2,'2009-1-1',836 union allselect 2,'2010-8-1',416 union allselect 2,'2011-9-1',799 select id, datepart(month,y_month) month,sum(case when datepart(year,y_month) = 2009 then num else 0 end) as '2009',sum(case when datepart(year,y_month) = 2010 then num else 0 end) as '2010',sum(case when datepart(year,y_month) = 2011 then num else 0 end) as '2011'from @tablegroup byid, datepart(month,y_month)order byid,datepart(month,y_month)
------解决方案--------------------
sum汇总一下就好了