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
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