SQL stored procedure to get concatenated string of all previous years records

Question

I have a table with time column in it. I am trying to create a stored procedure (SQL Server 2008) to get a string for each row with all previous years last value and current row value. For example, if I have the following table.

_________________
   time    value
_________________
mar-2009     1
_________________
may-2009     2
_________________
jan-2010     3
_________________
apr-2011     4
_________________
feb-2011     5
_________________
jan-2012     6
_________________

I am trying to get the following result

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

Stan Stan · Accepted Answer · 2014-06-15T08:16:59

Below are the sample, but for performance purposes its much better to make some changes to original table.

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

SQL stored procedure to get concatenated string of all previous years records

2 Answers