question

vsrini008 avatar image
vsrini008 asked

sum CSV data in a column using sql

![alt text][1] [1]: /storage/temp/2989-question.jpg
sql-server-2008sql-server-2005sqlsql-server-2008-r2
question.jpg (31.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered
create table #test (id int, data varchar(10), salary varchar(20)) insert into #test values (1,'a,b,c','100'), (2,'d,e,f,g,h','100,200,300'), (3,'i,j,k', '100,200') select ParsedData.*, SumData.TotalSalary from #test mt cross apply ( select str = mt.data + ',,' ) f1 cross apply ( select p1 = charindex( ',', str ) ) ap1 cross apply ( select p2 = charindex( ',', str, p1 + 1 ) ) ap2 cross apply ( select p3 = charindex( ',', str, p1 + 2 ) ) ap3 cross apply ( select p4 = charindex( ',', str, p1 + 3 ) ) ap4 cross apply ( select ID, Data1 = substring( str, 1, p1-1 ), Data2 = substring( str, p1+1, p2-p1-1 ), Data3 = substring( str, p1+3, p3-p1-1 ), Data4 = NULLIF(substring( str, p1+5, p4-p1-1 ),',') ) ParsedData INNER JOIN (select id, sum(convert(int,item)) AS TotalSalary from #test cross apply dbo.DelimitedSplit8K(salary,',') group by ID) as SumData ON ParsedData.ID = SumData.ID drop table #test Note, you need delimitedsplit8k from [here][1] [1]: http://www.sqlservercentral.com/articles/Tally+Table/72993/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.