表Ａ数据如下：
FYear FNum
2006 1
2006 2
2006 3
2007 4
2007 5
2007 6
按如下格式显示：
年度 2006 2007
汇总 6 15
------解决方案--------------------
declare @sql varchar(8000)
set @sql= ' '
select @[email protected]+ ',[ '+rtrim(FYear)+ ']=sum(case FYear when '+rtrim(FYear)+ ' then 1 else 0 end) '
from 表A group by FYear
set @sql= 'select ' '汇总 ' ' as 年度 '[email protected]+ ' from 表A '
exec(@sql)
------解决方案--------------------
declare @t table(FYear int, FNum int)
insert @t
select 2006, 1
union all
select 2006, 2
union all
select 2006, 3
union all
select 2007, 4
union all
select 2007, 5
union all
select 2007, 6
select (max(case fyear when 2006 then fnum else 0 end)) as '2006 ',
(max(case fyear when 2007 then fnum else 0 end)) as '2007 '
from (
select fyear,sum(fnum) as fnum from @t group by fyear
) derivedtbl
------解决方案--------------------
declare @sql varchar(8000)
set @sql= ' '
select @[email protected]+ ',[ '+rtrim(FYear)+ ']=sum(case FYear when '+rtrim(FYear)+ ' then FNUM else 0 end) '
from TABLE1 group by FYear
set @sql= 'select ' '汇总 ' ' as 年度 '[email protected]+ ' from TABLE1 '
exec(@sql)