当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - How to sum data from string

问题描述:

Have table :

id name

1 A1=7|A5=1|A10=5|A20=12|A50=8

2 A1=10|A5=2|A10=10|A20=14|A50=4

3 A1=3|A5=3|A10=5|A20=12|A50=8

.

.

Want sum all A1,A5,A10,A20,A50

Response must be like :

A1=20|A5=6|A10=20|A20=38|A50=20

How to do it ?

网友答案:

I also upvoted comment about changing table design but there are situations when somebody cannot do this. So here is the solution for this case and it's not so difficult. We call XML for assistance as usual when need to process formatted strings in XML columns.

-- Prepare data for solution testing
DECLARE @srctable TABLE (
    id      INT,
    name    VARCHAR(999),
    namexml XML
)

INSERT INTO @srctable
SELECT id, name, namexml FROM ( VALUES
(1, 'A1=7|A5=1|A10=5|A20=12|A50=8',   null),
(2, 'A1=10|A5=2|A10=10|A20=14|A50=4', null),
(3, 'A1=3|A5=3|A10=5|A20=12|A50=8',   null)
) v (id, name, namexml)

-- Transform source formatted string to XML string
UPDATE @srctable
SET namexml = CAST('<row><data ' + REPLACE(REPLACE(name, '|', '"/><data '), '=', '="') + '"/></row>' AS XML)

-- Final select from XML data
SELECT  SUM(x.data.value('(@A1)[1]',  'INT')) AS SUMA1,
        SUM(x.data.value('(@A5)[1]',  'INT')) AS SUMA5,
        SUM(x.data.value('(@A10)[1]', 'INT')) AS SUMA10,
        SUM(x.data.value('(@A20)[1]', 'INT')) AS SUMA20,
        SUM(x.data.value('(@A50)[1]', 'INT')) AS SUMA50
FROM @srctable AS t
CROSS APPLY t.namexml.nodes('/row/data') x (data)

You need to format your resulting string in any way you wish.

网友答案:

Variant without xml:

--------------------------------------------------------------------------------
-- Prepare data for solution testing
DECLARE @srctable TABLE ( id   INT
,                         NAME VARCHAR(999) )
INSERT INTO @srctable
VALUES ( 1, 'A1=7|A5=1|A10=5|A20=12|A50=8'   )
,      ( 2, 'A1=10|A5=2|A10=10|A20=14|A50=4' )
,      ( 3, 'A1=3|A5=3|A10=5|A20=12|A50=8'   )
--------------------------------------------------------------------------------
-- prepare temp table for using in split string
DECLARE @Tally TABLE ( N INT )
DECLARE @i AS INT = 1
WHILE @i != 1000
BEGIN
    INSERT INTO @Tally ( N  )
    VALUES             ( @i )
    SET @i = @i + 1
END
--------------------------------------------------------------------------------
--final query
;WITH cte AS
(
    SELECT id,
          (CASE WHEN CHARINDEX('|', S.string) > 0 
                THEN left(S.string, CHARINDEX('|', S.string) - 1)
                ELSE string END ) NAME
    FROM        @srctable AS E
    INNER JOIN  @Tally    AS T ON SUBSTRING('|' + NAME, T.N, 1) = '|'
            AND T.N <= LEN(NAME)
    CROSS APPLY (SELECT String = (CASE WHEN T.N = 1 
                                       THEN LEFT(E.NAME, CHARINDEX('|', E.NAME) - 1)
                                       ELSE SUBSTRING(E.NAME, T.N, 1000)    END )
                                  ) AS S
)

SELECT LEFT(NAME, CHARINDEX('=', NAME) - 1) AS NAME,
       SUM(convert(float,RIGHT(NAME, CHARINDEX('=', REVERSE(NAME)) - 1))) AS Value
FROM cte
GROUP BY LEFT(NAME, CHARINDEX('=', NAME) - 1)
分享给朋友:
您可能感兴趣的文章:
随机阅读: