表1
symbol year EPS
msft 2001 0.6
msft 2002 0.5
msft 2003 0.4
msft 2004 1.6
msft 2005 0.5
intl 2003 1.3
intl 2004 1.4
intl 2005 1.5
intl 2006 1.6
........
若何取得最晚的四项数据生成如下表
symbol Y1 Y2 Y3 Y4
msft 0.5 0.4 1.6 0.5
intl 1.3 1.4 1.5 1.6
.........
symbol year EPS
msft 2001 0.6
msft 2002 0.5
msft 2003 0.4
msft 2004 1.6
msft 2005 0.5
intl 2003 1.3
intl 2004 1.4
intl 2005 1.5
intl 2006 1.6
........
若何取得最晚的四项数据生成如下表
symbol Y1 Y2 Y3 Y4
msft 0.5 0.4 1.6 0.5
intl 1.3 1.4 1.5 1.6
.........
declare @t table(symbol varchar(10),year int,eps decimal(10,2))
insert @t
select 'msft', 2001, 0.6 union all
select 'msft', 2002, 0.5 union all
select 'msft', 2003, 0.4 union all
select 'msft', 2004, 1.6 union all
select 'msft', 2005, 0.5 union all
select 'intl', 2003, 1.3 union all
select 'intl', 2004, 1.4 union all
select 'intl', 2005, 1.5 union all
select 'intl', 2006, 1.6----提取最晚的四项数据
select * into #tmp from @t a where
not exists(select 1 from @t where symbol = a.symbol and year > a.year group by symbol having count(*) > 3)
order by symbol,year
----生成汇总分组ID
select *,id = (select count(1) from #tmp where symbol = a.symbol and year <= a.year) into #tmp2 from #tmp a
----生成静态汇总
select symbol,
Y1 = sum(case id when 1 then eps end),
Y2 = sum(case id when 2 then eps end),
Y3 = sum(case id when 3 then eps end),
Y4 = sum(case id when 4 then eps end)
from #tmp2 group by symbol
----动态汇总
declare @str varchar(4000)
set @str = ''
select @str = @str + ',Y' + rtrim(id) + ' = sum(case id when ' + rtrim(id) + ' then eps end)'
from #tmp2 group by id
set @str = 'select symbol' + @str + ' from #tmp2 group by symbol'
EXEC(@str)----清除测试环境
drop table #tmp,#tmp2
----结果
/*
symbol Y1 Y2 Y3 Y4
-------------------------------------------------
intl 1.30 1.40 1.50 1.60
msft .50 .40 1.60 .50
*/