• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Combining multiple answer rows into a single answer row for a given ID.

PLEASE HELP Experts!! I have tried several code solutions for my data set and currently unable to provide the complete solution with the given code...looking to better understand with the current result set.

Needing to combine multiple text answers into a single row for a given survey and question within (multiple answer_text per user_survey_id and survey_question_id combo). Currently, the data looks as such (many answers for many questions on many surveys for many employees):

user_survey_id   survey_question_id   answer_text
469463                  893                             Seeing my progress week to week
469463                  893                             The chance to win prizes
469467                  890                             Weight loss
469467                  890                             Increased energy level
469469                  901                             Biometric screening
469469                  901                             Pure Wellness online health risk appraisal
469469                  901                             Perfect attendance for 2010

Data generate with the following query:
 
SELECT	user_survey_id,
		vwRoute66Survey_MultipleAnswerSplit.survey_question_id,
		answer_text
FROM	vwRoute66Survey_MultipleAnswerSplit
		LEFT OUTER JOIN survey_answer ON vwRoute66Survey_MultipleAnswerSplit.SplitAnswer = survey_answer.survey_answer_id

Open in new window


How may I get the data in the following format (preferably without a cursor):
user_survey_id   survey_question_id   answer_text
469463                  893                             Seeing my progress week to week, The chance to win prizes
469467                  890                             Weight loss,  Increased energy level
469469                  901                             Biometric screening,  Pure Wellness online health risk appraisal, Perfect attendance for 2010


THANK YOU SO VERY MUCH Experts!!!
0
Levi Martin
Asked:
Levi Martin
  • 4
  • 4
2 Solutions
 
Ephraim WangoyaCommented:
try
0
 
Ephraim WangoyaCommented:
sorry
SELECT	user_survey_id,
		survey_question_id,
		Stuff((Select  ',' + answer_text
               From    survey_answer T2
               Where   T1.SplitAnswer = T2.survey_answer_id
               For xml Path('')), 1, 1, '') as [answers]
From vwRoute66Survey_MultipleAnswerSplit T1
Group By user_survey_id, survey_question_id

Open in new window

0
 
Levi MartinSenior Data AnalystAuthor Commented:
Errors out as vwRoute66Survey_MultipleAnswerSplit.SplitAnswer needs to be included in aggregate/grouping.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Ephraim WangoyaCommented:

Remove the group by clause, I dont think its neccessary in your case.

If you get duplicates, use distinct

eg

select distinct user_survey_id, .......
0
 
Levi MartinSenior Data AnalystAuthor Commented:
Everything is still left as multiple rows, no combination into a single row occurs.
0
 
Ephraim WangoyaCommented:
How exactly did you write the query?

Can you show the structure of the two tables and sample data

The query should have worked even with the group by clause
0
 
ralmadaCommented:
try

SELECT      distinct
      a.user_survey_id,
      a.vwRoute66Survey_MultipleAnswerSplit.survey_question_id,
      stuff((select ',' + answer_text from survey_answer where a.SplitAnswer = survey_answer_id), 1, 1, '') as answers
FROM      vwRoute66Survey_MultipleAnswerSplit a
0
 
Levi MartinSenior Data AnalystAuthor Commented:
The query is what results in the "user_survey_id,  survey_question_id ,answer_text" structure.. I've dumped the result set into a table labeled "tblRoute66Survey_MultipleAnswerSplit" to test these suggestions. At the moment everything is still resulting the same data set as the query...I will work with what I am seeing above.

Thank you both!!
0
 
Levi MartinSenior Data AnalystAuthor Commented:
Will post follow-up to the final code deployed. Wanted to provide credit for the work and samples submitted, thank you so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now