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 ?
-
7This will be very difficult. You should better change your table desgin. Never store multiple values in one column!juergen d– juergen d2014-09-11 10:19:19 +00:00Commented Sep 11, 2014 at 10:19
-
4Ok thanks, i will change designGeoVIP– GeoVIP2014-09-11 10:26:36 +00:00Commented Sep 11, 2014 at 10:26
Add a comment
|
2 Answers
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.
Comments
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)
