Sql: I have problems with getting the right data out my table

Question

0.00/5 (No votes)

See more:

This is my problem:
I would like to find out from at table, how many participants favoring effort 1-8.
They rank the different efforts between 1 to 3.
Dream scenario is that i can conclude that a and c like the effort 2 but different rank.
SQL should give something like:
A 2 1 C 2 2

Rank A B C D
1 2 8 5 1
2 3 7 2 3
3 7 3 8 7

What I have tried:

How ever I try I get the same result. Always the first rank that overrules the other rank. So if A says effort 2 on place 1, B says effort 2 also on place 1.

Posted 25-Nov-16 23:09pm

Member 12871563

Updated 30-Nov-16 0:02am

Add a Solution

Comments

Peter Leow 30-Nov-16 6:58am

It has been four days. I doubt anyone can understand you. If you have tried something, you should show your code. That example looks more an excel sheet than a database table.

1 solution

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)

JonPoley3 · Answer 1 · 2016-11-30T00:02:00

Hi,

I think the problem stems from the structure of your data. A normalised way of storing it might be in a table such as this:

CREATE TABLE #Efforts
(
	Participant nvarchar(15),
	Rank int,
	Effort int
)

The data you present would look like this:

C#

Participant	Rank	Effort
A	1	2
A	2	3
A	3	7
B	1	8
B	2	7
B	3	3
C	1	5
C	2	2
C	3	8
D	1	1
D	2	3
D	3	7

And then your query becomes trivial:

SELECT
	*
FROM
	#Efforts 
WHERE
	Effort = 2

which gives

C#

Participant	Rank	Effort
   A	      1	      2
   C	      2	      2

Hope I've understood your problem and that this solution helps.

Jon