稍微调整了一下,试试看declare @table table(id int,y_month datetime,num int) insert into @table select 0,'2009-12-1',519 union all select 0,'2009-2-1',463 union all select 0,'2009-9-1',723 union all select 0,'2010-1-1',199 union all select 0,'2010-8-1',371 union all select 0,'2011-1-1',278 union all select 0,'2011-12-1',437 union all select 0,'2011-2-1',441 union all select 1,'2009-1-1',370 union all select 1,'2009-9-1',230 union all select 1,'2010-1-1',247 union all select 1,'2011-11-1',821 union all select 2,'2009-1-1',836 union all select 2,'2010-8-1',416 union all select 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 @table group by id, datepart(month,y_month) order by id, datepart(month,y_month) id month 2009 2010 2011 ----------- ----------- ----------- ----------- ----------- 0 1 0 199 278 0 2 463 0 441 0 8 0 371 0 0 9 723 0 0 0 12 519 0 437 1 1 370 247 0 1 9 230 0 0 1 11 0 0 821 2 1 836 0 0 2 8 0 416 0 2 9 0 0 799(11 行受影响)
直接这样,同一个id月份是重复的,不符合啊。 select id ,right(y_month,2) as [month], (case (left(y_month,4)) when '2010' then num else null end) as [2010], (case (left(y_month,4)) when '2011' then num else null end) as [2011], (case (left(y_month,4)) when '2012' then num else null end) as [2012] from #table order by id,[month]
最终版本: select id ,right(y_month,2) as [month], sum(case(left(y_month,4)) when '2010' then rev else null end) as [2010], sum(case(left(y_month,4)) when '2011' then rev else null end) as [2011], sum(case(left(y_month,4)) when '2012' then rev else null end) as [2012] into final_table from data_table group by id,right(y_month,2) order by idalter table final_table alter column [month] intselect * from final_table order by id,[month]
insert into @table
select 0,'2009-12-1',519 union all
select 0,'2009-2-1',463 union all
select 0,'2009-9-1',723 union all
select 0,'2010-1-1',199 union all
select 0,'2010-8-1',371 union all
select 0,'2011-1-1',278 union all
select 0,'2011-12-1',437 union all
select 0,'2011-2-1',441 union all
select 1,'2009-1-1',370 union all
select 1,'2009-9-1',230 union all
select 1,'2010-1-1',247 union all
select 1,'2011-11-1',821 union all
select 2,'2009-1-1',836 union all
select 2,'2010-8-1',416 union all
select 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 @table
group by
id,
datepart(month,y_month)
order by
id,
datepart(month,y_month)
id month 2009 2010 2011
----------- ----------- ----------- ----------- -----------
0 1 0 199 278
0 2 463 0 441
0 8 0 371 0
0 9 723 0 0
0 12 519 0 437
1 1 370 247 0
1 9 230 0 0
1 11 0 0 821
2 1 836 0 0
2 8 0 416 0
2 9 0 0 799(11 行受影响)
select id ,right(y_month,2) as [month],
(case (left(y_month,4)) when '2010' then num else null end) as [2010],
(case (left(y_month,4)) when '2011' then num else null end) as [2011],
(case (left(y_month,4)) when '2012' then num else null end) as [2012]
from #table
order by id,[month]
select id ,right(y_month,2) as [month],
sum(case(left(y_month,4)) when '2010' then rev else null end) as [2010],
sum(case(left(y_month,4)) when '2011' then rev else null end) as [2011],
sum(case(left(y_month,4)) when '2012' then rev else null end) as [2012]
into final_table
from data_table
group by id,right(y_month,2)
order by idalter table final_table
alter column [month] intselect * from final_table order by id,[month]