Convert column from string of numbers to a subtotal on select statment

Question

This probably very basic but I have struggled to figure out. I have looked around but couldn't find any similar problem. I am using SQL Server 2008 R2

I have a view:

ID | VALUE
1 | 151;181;179;185;160;187
2 | 185;160
3 | 151;181;179;185;150
4 | 185;160;187
5 | 187

I want a select statement to produce this results:

ID | VALUE
1 | 1043
2 | 345
3 | 846
4 | 532
5 | 187

• Mark Sinkinson: 'I have a view'. Does this view look at an underlying table where the data is stored in the format you want?

Answer 1

This is definitely not a good data model.

If you are stuck with it you could use

SELECT ID, 
 X.value('sum(/x/text())', 'FLOAT') as Value 
FROM YourView
CROSS APPLY (VALUES(CAST('<x>' + REPLACE(VALUE, ';', '</x><x>') + '</x>' AS xml))) V(X)

Online Demo

Answer 2

Probably the best way to do this is to use a function to handle splitting the string, along with a cross apply and group by to summarize the data for returning.

CREATE FUNCTION dbo.SplitStrings_XML
 (
 @List NVARCHAR(MAX) ,
 @Delimiter NVARCHAR(255)
 )
RETURNS TABLE
 WITH SCHEMABINDING
AS
 RETURN
 ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
 FROM ( SELECT x = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter,
 '</i><i>')
 + '</i>').query('.')
 ) AS a
 CROSS APPLY x.nodes('i') AS y ( i )
 );
GO
DECLARE @Data TABLE
 (
 ID INT ,
 ValueToAdd VARCHAR(200)
 );
INSERT INTO @Data
 ( ID, ValueToAdd )
VALUES ( 1, '151;181;179;185;160;187' )
, ( 2, '185;160' )
, ( 3, '151;181;179;185;150' )
, ( 4, '185;160;187' )
, ( 5, '187' );
SELECT d.ID ,
 SUM(CAST(x.Item AS INT)) AS TotalValue
FROM @Data AS d
 CROSS APPLY dbo.SplitStrings_XML(d.ValueToAdd, ';') AS x
GROUP BY ID;

The function referenced is from an extensive series on string splitting and performance available at https://sqlperformance.com/2012/07/t-sql-queries/split-strings