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 | | AdultDesired output: **
clientvisit_id | client_id | members_present | patient_category**
141001 | 2001 | Patient | AdultSelect 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'
Asked by Bryan
(11 rep)
Jun 25, 2020, 08:23 PM
Last activity: Jul 27, 2025, 11:04 PM
Last activity: Jul 27, 2025, 11:04 PM