Posted by Ed Murphy on 03/30/07 18:29
Matt F wrote:
> VoterID AnswerText AnswerID
> 5 Comments here 2058
> 5 <NULL> 2057
> 5 <NULL> 2059
>
>
> AnswerID is found in the Answer table which corresponds (in this case)
> to 2057="Technology" and 2059="Satisfied"
>
>
> What I need is one recordset with the following format.
>
> VoterID Department Rating
> ---------- ----------------- ------------
> 5 Technology Satisfied
Insert standard "post the CREATE TABLE statement for the Answer table"
rant here. Assuming that it looks something like this:
AnswerID | AnswerType | AnswerValue
---------+------------+------------
2057 | Department | Technology
2059 | Rating | Satisfied
then you can do:
select va.VoterID,
ad.AnswerValue as Department,
ar.AnswerValue as Rating
from VoterAnswers va
join Answer ad on va.AnswerID = ad.AnswerID
and ad.AnswerType = 'Department'
join Answer ar on va.AnswerID = ar.AnswerID
and ar.AnswerType = 'Rating'
[Back to original message]
|