再问一句SQL
表Ａ数据如下：
FYear FNum
2006 1
2006 2
2006 3
2007 4
2007 5
2007 6
按如下格式显示：
年度 2006 2007
汇总 6 15
select fyear as 年度 sum(FNum) as 汇总 from table group by fyear
这样显示的不对
年度 汇总
2006 6
2007 15
[解决办法]
select
fyear ,
sum(case when fyear =2006 then FNum else 0 end) as [2006],
sum(case when fyear =2007 then FNum else 0 end) as [2007]
from 表名
group by fyear
[解决办法]
--借用下数据：
create table T(FYear int, FNum int)
insert into T select 2006, 1
insert into T select 2006, 2
insert into T select 2006, 3
insert into T select 2007, 4
insert into T select 2007, 5
insert into T select 2007, 6
select fyear,sum(case when fyear= '2006 ' then fnum else 0 end) as [2006] ,sum(case when fyear= '2007 ' then fnum else 0 end) as [2007]
from t group by fyear
declare @sql varchar(8000)
set @sql= 'select fyear '
select @sql=@sql+ ', '+ 'sum(case when fyear= ' ' '+ rtrim(fyear)+ ' ' ' then fnum else 0 end ) as [ '+ rtrim(fyear)+ '] '
from (select distinct fyear from t ) a
set @sql=@sql+ ' from t group by fyear '
exec(@sql)
fyear 2006 2007
----------- ----------- -----------
2006 6 0
2007 0 15
