Monday, August 27, 2018

Sum Comma Separated values in SQL Server

Scenario: Comma Separated values are stored in a single column.
Need to show the output summation of values in single column.

Here is the expected Before/After:


Table structure:

create table SumCommaSeparated
(
  id int identity(1,1),
  value varchar(200)
)

Insert Values into table:

insert into SumCommaSeparated values('20,12,14,34');
insert into SumCommaSeparated values('20,15,14,34,82,90');
insert into SumCommaSeparated values('200');

Create SQL function to sum up and show the output:

Create FUNCTION [dbo].[fnSplit]
(
@list  NVARCHAR(2000)

RETURNS TABLE AS
RETURN

   WITH SplitRecords(LoopStart, LoopEnd) AS (
--Split Column values till End
     SELECT LoopStart = 1,
        LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS, @list + ',')
     UNION ALL
     SELECT LoopStart = LoopEnd + 1,
            LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS,@list + ',', LoopEnd + 1)

     FROM   SplitRecords
WHERE  LoopEnd > 0
  )
  SELECT sum(cast(LTRIM(RTRIM(SUBSTRING(@list, SplitRecords.LoopStart,
CASE WHEN SplitRecords.LoopEnd > 0 THEN SplitRecords.LoopEnd - SplitRecords.LoopStart ELSE 0 END))) as bigint)) 
--Cast your output according to requirement
AS VALUE
  FROM   SplitRecords
  WHERE  LoopEnd > 0


SQL Script:

select SCSep.id,SCSep.value BeforeAddition,fn.VALUE as AfterAddition
from SumCommaSeparated SCSep
cross apply [dbo].[fnSplit](SCSep.value) as fn




No comments:

Post a Comment