aggregation of comma-separated values in-position

Question

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

Not the greatest database design, though if consistent, we can make things work. WIll you _always_ have 6 items in the counter_values field? — Eli, Aug 02 '17 at 14:19
I would agree with @Eli, this is a horrible design as it violates 1NF. You have to jump through quite a few hoops here because of your design. If this was properly normalized this would be super simple. — Sean Lange, Aug 02 '17 at 14:22
By normalizing it with a second table LoanNo, counter_type, Counter_value you can do whatever you want. I'll let someone else answer how to split a column because you could easily search for that. — KeithL, Aug 02 '17 at 14:24
I agree with you that there could be a better design. I believe that I should make 6 column instead of one counter_value column but the actual business case is more complex than this. There are more than 4000 counter values are coming in some raw file and if I use different counters for each value then columns list will be large. Please suggest some design that can save storage and also works when selecting data from table. — hieko, Aug 02 '17 at 14:28

score 5 · Answer 1 · answered Aug 02 '17 at 14:31

Since you have denormalized data you will first have to split this into columns, do the aggregation and then recreate the delimited column. There are plenty of splitters out there but here is my favorite for this type of thing. http://www.sqlservercentral.com/articles/Tally+Table/72993/ The main advantage of this splitter is that it returns the position of each value which most other splitter do not.

Utilizing that splitter you can do this like this.

with AggregateData as
(
    select t.loanno
        , s.ItemNumber
        , TotalValue = sum(convert(int, s.Item))
    from @tbl t
    cross apply dbo.DelimitedSplit8K(t.counter_value, ',') s
    group by t.loanno
        , s.ItemNumber
)

select ad.loanno
    , STUFF((select ',' + convert(varchar(10), ad2.TotalValue)
        from AggregateData ad2
        where ad2.loanno = ad.loanno
        order by ad2.ItemNumber
        FOR XML PATH('')), 1, 1, '')
from AggregateData ad
group by ad.loanno

John Cappelletti · Answer 2 · 2017-08-02T14:48:06.463

Sean's would be my first choice (+1).

However, if you have a known (or fixed) number of positions, consider the following:

Example

Select A.loanno
      ,NewAggr  = concat(sum(Pos1),',',sum(Pos2),',',sum(Pos3),',',sum(Pos4),',',sum(Pos5),',',sum(Pos6))
 From  @tbl A
 Cross Apply (
                 Select Pos1 = n.value('/x[1]','int')
                       ,Pos2 = n.value('/x[2]','int')
                       ,Pos3 = n.value('/x[3]','int')
                       ,Pos4 = n.value('/x[4]','int')
                       ,Pos5 = n.value('/x[5]','int')
                       ,Pos6 = n.value('/x[6]','int')
                  From  (Select cast('<x>' + replace(A.counter_value,',','</x><x>')+'</x>' as xml) as n) X
             ) B
 Group By A.loanno

Returns

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

If it Helps with the Visualization, the CROSS APPLY Generates

aggregation of comma-separated values in-position

2 Answers2