日期:2014-05-19  浏览次数:20446 次

问个简单查询.解决马上给分。
表A数据如下:
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 @sql=@sql+ ',[ '+rtrim(FYear)+ ']=sum(case FYear when '+rtrim(FYear)+ ' then 1 else 0 end) '
from 表A group by FYear

set @sql= 'select ' '汇总 ' ' as 年度 '+@sql+ ' 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 @sql=@sql+ ',[ '+rtrim(FYear)+ ']=sum(case FYear when '+rtrim(FYear)+ ' then FNUM else 0 end) '
from TABLE1 group by FYear

set @sql= 'select ' '汇总 ' ' as 年度 '+@sql+ ' from TABLE1 '

exec(@sql)