I've got two tables that store questions and possible answers. The questions table can have multiple possible answers to it.
I want to display the possible answers it can have per question.. For instance lets say I have two questions
1) Hows the weather?
2) Do you love MI?
And the other table I have possible answers
1) Good
1) Fair
2) Yes
2) No
My expecting result would be
1 Good, Fair
2 Yes, No
But instead I get:
1 Good, Fair, Yes, No
2 Yes, No, Good, Fair
Here's what I am doing...
SELECT x.SurveyQuestionID, PossibleAnswers = STUFF((
SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa
INNER JOIN SurveyQuestion sq
ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID
I'm getting the right number of rows due to the group by, but the possible answers seems to be giving me every possible answer for each question.
STUFF
- sqlfiddle.com/#!6/f25dc/2 to tie the outer data to the list.