2

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
  • 3
    'I have a view'. Does this view look at an underlying table where the data is stored in the format you want? – Mark Sinkinson Oct 31 '16 at 14:02
5

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.