Sql Issue with Survey results

sql, sql-server

Solution

You have to use the keyword STUFF to get the above result.

QUERY:

SELECT DISTINCT T1.MEMBER_ID,T1.QUESTION_VARIABLE,
  STUFF((SELECT DISTINCT ',' + T2.RESPONSE_ID
         FROM TEST T2
         WHERE T1.QUESTION_VARIABLE = T2.QUESTION_VARIABLE
            FOR XML PATH('') ),1,1,'') AS RESPONSE_ID,
  STUFF((SELECT DISTINCT ',' + T2.LABEL
         FROM TEST T2
         WHERE T1.QUESTION_VARIABLE = T2.QUESTION_VARIABLE
            FOR XML PATH('') ),1,1,'') AS LABEL         

FROM TEST T1
;

HERE IS THE LINK TO SQL FIDDLE http://sqlfiddle.com/#!3/64515/3

Problem

Hi I have a query returning this ``` member_id question_variable response_id label ----------- -------------------------------------------------- ----------- ---------------------------- 35 area 15 Sydney (Metro) 35 relationship_status 8 Single 35 education 31 Bachelor Degree 35 house_hold_income 4 $75,001 to $100,000 35 pets 36 Dog 35 pets 37 Fish ``` How do i detect duplicate results such as pets and have response_id = 36,37 and label = Dog, Fish like so ``` member_id question_variable response_id label ----------- -------------------------------------------------- ----------- ---------------------------- 35 area 15 Sydney (Metro) 35 relationship_status 8 Single 35 education 31 Bachelor Degree 35 house_hold_income 4 $75,001 to $100,000 35 pets 36,37 Dog,Fish ```

Original source