15,906,467 members
1.00/5 (1 vote)
See more:
```Username       WorkArea      Scope      Qulaity%
aa,bb,cc        xyz           zyx        100
aa,bb,dd        xyz           zyx        99```

Now i want the sum of the qulaity% based on the username.
For Example
```Username      total
aa            199
bb            199
dd            99
cc            100
```

I want to sum the quality of each user but they are delimited by commas. As `aa` is in both the rows its `Quality` sum should be `199`. As `cc` is in the single row his sum should `100`, etc.
Posted
Updated 11-May-15 20:12pm
v3
Maciej Los 12-May-15 1:55am
Based on what codition? Did you forget about 'cc'?
King Fisher 12-May-15 1:59am
what is the logic? it not clear to understand the logic, update your question pls.

## Solution 1

Check this:
SQL
```DECLARE @tmp TABLE (Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)

INSERT INTO @tmp (Username, WorkArea, Scope, Quality)
VALUES('aa,bb,cc,ee,ff,gg,hh,ii', 'xyz', 'zyx', '100'),
('aa,bb,dd,ii,kk', 'xyz', 'zyx', '99'),
('cc,gg,ee,hh,kk', 'xyz', 'zyx', '77')

;WITH UserTotals AS
(
-- initial values
--multiple users
FROM @tmp
UNION ALL
--single user only
SELECT Username AS SingleUser, Quality, NULL AS Remainder
FROM @tmp
-- here starts recursive part
UNION ALL
SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM UserTotals
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT Remainder AS SingleUser, Quality, NULL AS Remainder
FROM UserTotals
WHERE CHARINDEX(',', Remainder)=0
)
SELECT SingleUser, SUM(Quality) AS Total
FROM UserTotals
GROUP BY SingleUser```

Result:
```aa	199
bb	199
cc	177
dd	99
ee	177
ff	100
gg	177
hh	177
ii	199
kk	176```

Note: above example uses CTE[^] (recursive query) to split `Username` into single user name. For further information, please see: Using Common Table Expressions[^]

[EDIT]
CTE query has been updated according to OP comments.

v5
Sergey Alexandrovich Kryukov 12-May-15 2:11am
...but, speaking of understanding by the inquirer: this is nice, you can use it to explain things; a 5.
—SA
Maciej Los 12-May-15 2:13am
Thank you, Sergey.
King Fisher 12-May-15 2:17am
nice maciej master :) ma 5.
Maciej Los 12-May-15 3:08am
Thank you, King ;)
Maciej Los 12-May-15 3:09am
It's global solution! Have you tried?

## Solution 2

Don't use different data items in one column, comma-separated or separated in any other way. This a total abuse of relational model and programming in general.

All attributes in relational database should be atomic. In other words, you should have separate table of elements as aa, bb or cc. Some other table could be called "group", and another one could be called "groupMembership"; it could associate elements with groups. And then, in the table you show, include foreign key referencing a group, instead of "UserName". This way, all queries will be done in SQL, without parsing your lists. This schema could vary, depending on your goals, constraints, etc. I hope you got the idea.

—SA

Maciej Los 12-May-15 2:08am
Holy true! +5!
Sergey Alexandrovich Kryukov 12-May-15 2:10am
Thank you, Maciej. Not sure the inquirer will understand this. Let's see...
—SA
King Fisher 12-May-15 2:20am
Definitely, the OP will understand sir. its more clear 5+
Sergey Alexandrovich Kryukov 12-May-15 2:26am
Thank you.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Dave Kreskowiak 20 OriginalGriff 20 George Swan 10 Hemil Gandhi -59
 Pete O'Hanlon 1,750 OriginalGriff 1,387 Richard Deeming 550 Dave Kreskowiak 493 Richard MacCutchan 355

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900