获取所有以前年份记录的串联字符串的SQL存储过程

Question

我有一个表，里面有时间列。我正在尝试创建一个存储过程（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"
____________________________________________________

Answer 1

下面是示例，但出于性能目的，对原始表进行一些更改要好得多。

-- 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