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
Select
cv.clientvisit_id,
cv.client_id,
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
a1.answer_id,
a1.answer
From Answer a1
Where a1.question_id = '532096'
) as pc on sva.answer_id = pc.answer_id
Inner Join (Select
a2.answer,
a2.answer_id
From Answer a2
Where a2.question_id = '532093'
) as mp on sva.answer_id = mp.answer_id
Where
Forms.form_id = '246'
Best Answer
If you wrap
around your current query you'll collapse those rows into one, removing the blanks.