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

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