2

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.

3
  • You need a WHERE clause in your STUFF - sqlfiddle.com/#!6/f25dc/2 to tie the outer data to the list.
    – Taryn
    Apr 17, 2015 at 19:56
  • 1
    How in the heck did I miss that....
    – JonH
    Apr 17, 2015 at 19:57
  • Depending on your actual query you could shorten your code to - sqlfiddle.com/#!6/f25dc/4
    – Taryn
    Apr 17, 2015 at 19:58

1 Answer 1

4

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              INNER JOIN SurveyQuestion sq
                ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
              WHERE x.SurveyQuestionID = sq.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Or to shorten the code, you could even rewrite the query to:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              WHERE x.SurveyQuestionID = sqpa.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestion AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Both will give you the same result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.