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'

• Next in forum: Converting a date field to the month
• Prev in forum: DTS SQL JOB hangs even after the successful completion of the DTS
• Thread view: Re: Combine multiple records into single row

[Reply to this message]