Re: Combine multiple records into single row

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'
.