SQLTeam.com | Weblogs | Forums

Aggregation of values from comma seperated list


Hi team,

I have one table where there is two column loan no and counter_value.

Against each loan no there is the list of comma separated values are stored.

declare @tbl table (loanno varchar(100) , counter_value varchar(200) )

insert into @tbl

I need to do grouping according to loan no and in-position aggregation (summation) on counter values.
I need the output like below.

loanno counter_value
pr0021 2000,600,800,390,1800,530


Thanks to the awesome DelimitedSplit8K by @JeffModen you can do:

with cte
  as (select a.loanno
            ,sum(cast(b.item as int)) as item
        from @tbl as a
             cross apply dbo.delimitedsplit8k(a.counter_value,',') as b
       group by a.loanno
select a.loanno
      ,stuff((select ','+trim(str(b.item))
                from cte as b
               where b.loanno=a.loanno
                 for xml path(''),type
       as counter_value
  from cte as a
 group by a.loanno


Thank you for the kind words, @bitsmed.