Problem with Comma Seprated Values in sql

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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 11-May-15 19:45pm

Member 11683119

Updated 11-May-15 20:12pm

Maciej Los

v3

Add a Solution

Comments

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.

2 solutions

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

Posted 11-May-15 20:06pm

Sergey Alexandrovich Kryukov

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2015-05-11T20:05:00

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
	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
        --single user only
	SELECT Username AS SingleUser, Quality, NULL AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Username)=0
        -- 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.