Check this:
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
(
SELECT LEFT(Username, CHARINDEX(',', Username)-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS Remainder
FROM @tmp
WHERE CHARINDEX(',', Username)>0
UNION ALL
SELECT Username AS SingleUser, Quality, NULL AS Remainder
FROM @tmp
WHERE CHARINDEX(',', Username)=0
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.