Aggregation of values from comma seperated list

Transact-SQL
1

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
values('pr0021','1000,200,300,100,800,230'),
('pr0021','500,300,300,100,600,200'),
('pr0021','500,100,200,190,400,100')

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

2

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

with cte
  as (select a.loanno
            ,b.itemnumber
            ,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
               ,b.itemnumber
     )
select a.loanno
      ,stuff((select ','+trim(str(b.item))
                from cte as b
               where b.loanno=a.loanno
                 for xml path(''),type
             ).value('.','nvarchar(max)')
            ,1,1,''
            )
       as counter_value
  from cte as a
 group by a.loanno
;
1 Like
3

Thank you for the kind words, @bitsmed.