August 30, 2019 at 3:25 pm
Hello, I am working on a questionnaire, and based how the user answers these questions, we want to identify the most popular values selected.
For example, lets say we have 10 questions. For the first series of questions (1-5), the value will be "A" or "B". The second series of questions (6-10) will be "C" or "D".
Since there is 5 questions per series, I want to determine which value "A" or "B" and "C" or "D" was picked the most per series.
Here is my sample code:
-- DROP TABLE #Q
CREATE TABLE #Q (QID int IDENTITY(1,1), Q1 char(1), Q2 char(1), Q3 char(1), Q4 char(1), Q5 char(1), Q6 char(1), Q7 char(1), Q8 char(1), Q9 char(1), Q10 char(1))
INSERT INTO #Q (Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10) VALUES ('A', 'B', 'B', 'A', 'B','C','C','C','C','D')
SELECT * FROM #Q
In this example, "B" and "C" would be the most popular or dominate value for each of their respective series.
How can I build the logic to do this? Many thanks in advance!
August 30, 2019 at 3:49 pm
Your table design is not normalised.
How about using something like (UserId, QuestionNumber INT, Response CHAR(1))?
Then your query becomes easy. And increasing the number of questions in future does not require a software release!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2019 at 3:53 pm
Ah, got it! Thanks again Phil!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply