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