我有一个表，里面有时间列。我正在尝试创建一个存储过程（SQL Server2008)，为每一行获取一个字符串，其中包含所有以前年份的上一个值和当前行值。例如，如果我有下表。
_________________ time value _________________ mar-2009 1 _________________ may-2009 2 _________________ jan-2010 3 _________________ apr-2011 4 _________________ feb-2011 5 _________________ jan-2012 6 _________________
我试图得到以下结果
____________________________________________________ time value Result ____________________________________________________ mar-2009 1 "2009,1" ____________________________________________________ may-2009 2 "2009,2" ____________________________________________________ jan-2010 3 "2009,2,2010,3" ____________________________________________________ apr-2011 4 "2009,2,2010,3,2011,4" ____________________________________________________ feb-2011 5 "2009,2,2010,3,2011,5" ____________________________________________________ jan-2012 6 "2009,2,2010,3,2011,5,2012,6" ____________________________________________________
下面是示例，但出于性能目的，对原始表进行一些更改要好得多。
-- first we need to extract months/years to get comparable and sortable values
-- otherwise we can't select "all previous years" and "last value".
;with converted as (
select
time,
right(time, 4) as year,
datepart(m, '2000-' + left(time, 3) + '-01') as month,
right(time, 4) + ',' + convert(varchar(16), value) as concatenated,
value
from
YourTableName --TODO: Replace this with your table name
)
select
time,
value,
-- using xml for string concatenation
isnull((
select
prev.concatenated + ',' as 'text()'
from
converted as prev
where
prev.year < main.year
and prev.month = (select max(month) from converted lookup where lookup.year = prev.year)
for
xml path('')
),'')
+ main.concatenated
from
converted as main
