I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone needs more information to help. This should be fairly easy and I'm just having a brain fart I think. I need to combine multiple answers into a single row as separate columns.

Here is my query as it is and it does return all the answers but every answer is generating a separate row. There will only ever be one answer for each question per visit id.

There are a few catches to working with this system. At it's heart it's SQLServer, however queries are restricted to starting with 'select' making temp tables a bit more difficult. There can be no spaces, no blank lines nothing before your select. This is their version of security I guess. All reports are written through a web interface no direct access to the db in any way.

Current Output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |                   |
141001          |  2001       |                   |      
141001          |  2001       |  Patient          |      
141001          |  2001       |                   |  Adult       

Desired output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |  Patient          |  Adult   
  mp.answer as members_present,
  pc.answer as patient_category

From ClientVisit cv
  Inner Join SavedVisitAnswer sva On sva.clientvisit_id = cv.clientvisit_id
  Inner Join Question q On sva.question_id = q.question_id
  Inner Join Category cat On q.category_id = cat.category_id
  Inner Join FormVersion fv On cat.form_ver_id = fv.form_ver_id
  Inner Join Forms On fv.form_id = Forms.form_id
  Inner Join (Select
     From Answer a1
     Where a1.question_id = '532096'
  ) as pc on sva.answer_id = pc.answer_id 
  Inner Join  (Select
     From Answer a2
     Where a2.question_id =  '532093'
  ) as mp on sva.answer_id = mp.answer_id

  Forms.form_id = '246'
  • Why are there rows with empty answers? Why don't you exclude them?
    – mustaccio
    Jun 25 '20 at 21:28
  • This system stores notes to answers as answers stored in yet a different table. I just haven't excluded the notes yet. I thought about including them but STUFF does not like to work with such distant indirect relations.
    – Bryan
    Jun 26 '20 at 12:00

If you wrap

  <your query goes here>
) as x
group by

around your current query you'll collapse those rows into one, removing the blanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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