表结构如下: a b c d
1 2 3 2010
2 3 4 2011
3 5 1 2012
...............
需要输出的为: 2010 2011 2012
6 9 9 ----->(相应年份下的abc之和)
1 2 3 2010
2 3 4 2011
3 5 1 2012
...............
需要输出的为: 2010 2011 2012
6 9 9 ----->(相应年份下的abc之和)
调试欢乐多
max(case when d = 2010 then a+b+c end) as 2010,
max(case when d = 2011 then a+b+c end) as 2011,
max(case when d = 2012 then a+b+c end) as 2012
from
表
create table tb(a int,b int,c int,date varchar(10))
insert into tb
select 1,2,3,'2010' union all
select 2,3,4,'2011' union all
select 3,5,1,'2012'
godeclare @sql varchar(max)
select @sql = isnull(@sql + ',','') + 'sum(case date when ''' + date + ''' then a + b + c else 0 end)[' + date + ']'
from (select distinct date from tb)u
select @sql = 'select ' + @sql + ' from tb'
exec(@sql)drop table tb/*
2010 2011 2012
----------- ----------- -----------
6 9 9(1 行受影响)
sum(case when d='2010' then a+b+c else 0 end) as 2010,
sum(case when d='2011' then a+b+c else 0 end) as 2011,
sum(case when d='2012' then a+b+c else 0 end) as 2012
from Tab