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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With