Aggregating Comma Separated Values at Position
I have one table that has two columns with no credit and counter_value.
There is no comma separated list of stored values against each credit.
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 the grouping according to the sum (sum) of the loan and the sum by counters. I need the output as shown below.
loanno counter_value
pr0021 2000,600,800,390,1800,530
source to share
Since you have denormalized data, you first need to split this into columns, do the aggregation, and then recreate the separating column. There are many splitters out there, but here's 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 delimiters do not have.
Using this separator, you can do it 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
source to share
Sean was 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 rendering, CROSS APPLY generates
source to share