January 28, 2008 at 9:06 am
Hi, im a newbie here - so thanks for your time in reading my post.
I have a complicated problem (well, it seems tricky to me 🙂 )
Background:
im writting a questionnaire system that can essentially hold answers from lots of different questionaires in one table, then i will piece together the users entire questionnaire afterwords.
The reason being is i dont need to add lots of different tables for each new questionnaire, i can just add the answers to the same table then write a new SP to get the results.
so, example of data in the answers table:
qId = questionnaire Id
q# = questionNumber
[id] [uniqueUser] [qId] [q#] [answer]
110AAA123458315
111AAA123458323
112AAA123458331
119 XXX123456 1 1 1
217BBB123458315
218BBB123458321
219BBB123458332
so, the output i need will look like
RESULTS FOR QUESTIONNAIRE ID=3
id, username, q1, q2, q3
1, AAA123458, 5, 3, 1
2, BBB123458, 5, 1, 2
Now, this is as far as i have gotten on paper, i may well be barking up the wrong tree.
1. Get unique users where qID = @qID
2. For each unique user add answers to a temp table
thats were my mind explodes to be honest, im not sure of the best way to implement this.
I did manage to get a very bloated query working, but it fell over when i had more than one user in the database, becuase it was returning multiple results for a users answers.
the query looked like this
SELECT DISTINCT username, waveId,
(SELECT answer
FROM dbo.tbl_answers
WHERE (questionNumber = 1)) AS Q1,
(SELECT answer
FROM dbo.tbl_answers AS tbl_answers_2
WHERE (questionNumber = 2)) AS Q2,
(SELECT answer
FROM dbo.tbl_answers AS tbl_answers_2
WHERE (questionNumber = 3)) AS Q3)
FROM dbo.tbl_answers AS tbl_answers_1
WHERE (waveId = 3) AND (username =
(SELECT DISTINCT username
FROM dbo.tbl_answers AS tbl_answers_3))
thanks again for looking at this and helping an SQL newb, and a newb to your community 🙂
January 28, 2008 at 9:23 am
First - you're right to pursue putting the answers in one single table. So keep following that data structure along - you're on the right track there.
As to gettting your output, you're going to want to look up the PIVOT operation, which will help you turn your answers table in the the "grid" look you want.
Your code would look something like:
selectROW_NUMBER() over (order by UniqueUser),
UniqueUser,
[1] as q1,
[2] as q2,
[3] as q3
from(
select uniqueUser,
qnumber,
answer
from answers
where qid=3
) as ToBePivoted
PIVOT
(max(answer) for Qnumber in [1],[2],[3]) as Pivoted
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 9:27 am
First thing I'd suggest it normalise your tables 🙂
If you have a table of users, then finding the unique users is much simplified. Perhaps a table for the questionaires (or perhaps not) and then the answers. So for starters
Create table Users (
UserID int identity primary key,
UserName varchar(50)
)
Create table QuestionnaireAnswers (
UserID int,
QuestionnaireID int,
QuestionNo int,
Answer varchar(10) -- Dunno the type
)
ALTER TABLE QuestionnaireAnswers
ADD Constraint pk_QuestionnaireAnswers PRIMARY KEY (UserID,QuestionnaireID,QuestionNo)
With that, to get the unique users is as simple as querying the users table. Users for a questionnaire, join the two tables and filter on the questionnaireID
As for the other, are you on SQL 2005 (I assume so, since this is the SQL 2005 forum)
If so, you might be able to use Pivot to get you the rows as columns for each user.
SELECT * FROM
(SELECT UserID, QuestionNo, Answer
FROM QuestionnaireAnswers
WHERE QuestionnaireID = @QuestionnaireID
) InnerQuery
Pivot (max(Answer) FOR QuestionNo IN ([1],[2],[3])) AS pvt
Syntax is not guaranteed, as I haven't tested. An aggregate is required, that's why I use Max. If a user only answers a questionnaire once, there will only be one answer per question, so the max is the value
Be careful of subqueries in the select list of a query. SQL has to run the sub query once for each row of the outer query, so they're almost guaranteed to run really slow.
Does that help?
Edit: Beaten to it... 😛
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 9:32 am
hi, thanks to you and the above poster for the excellent and swift replies.
I do already have the following tables in my system
users (each user is unique by username)
questions
questionOptions
questionnaires (each questionnaire is a list of question Ids)
answers
sorry for not mentioning this, i thought i would really only need answers.
i will look into a PIVOT operation and get back to you.
cheers
March 4, 2008 at 9:23 am
Hello,
the above advice worked spledidly however i now have another question
below is the query
SELECT *
FROM (SELECT username, questionId, answer
FROM tbl_answers_control_group
WHERE waveId = 1) InnerQuery PIVOT (max(answer) FOR questionId IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17],
[18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [40], [41], [42], [43], [44], [45])) AS pvt
in each answer record i record the date+time of when the answer was taken.
For each pivot row i need to show how long the entire questionnaire took
I realise i need to do max(answerDate)-min(answerDate) however, i cant seem to get it to fit into the PIVOT table.
any suggestions?
March 4, 2008 at 9:41 am
The short answer is - the "new" PIVOT syntax only allows for one type of aggeregation, so you can't incorporate that into the PIVOT statement.
Now - given that short answer - there are two ways AROUND that "problem":
1. keep the existing query you have, and add another sub-query that independently covers the time calculation. On the up-side - it allows you to keep your existing code, however - it then requires having a second query on the answers table, meaning a second pass through the data, some time to join the two, etc.... In other words - a less performant query
2. Use the older syntax (prior to PIVOT) to perform the same logical contruct. That means "starting over" on the query, but you will end up with something that runs faster, and ultimately performs better in general.
The old-style syntax looks something like this (making some assumptions on field names, etc...adapt to your actual data model).
SELECT username,
max(case when questionId=1 then answer else null end) as [1],
max(case when questionId=2 then answer else null end) as [2],
max(case when questionId=3 then answer else null end) as [3],
etc... --finish up the questions
dateadd(ms,datediff(ms,max(answerdate),min(answerdate)),0) as TimeElapsed --this is a datetime construct so format appropriately
FROM tbl_answers_control_group
where waveId =1 --asssuming this is your questionnaire ID
GROUP BY Username
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 10:11 am
Matt,
i changed my queries to go with your suggested "old method" and its working like a charm,
thank you very much. Have a cyber-beer on me
March 4, 2008 at 10:36 am
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply