Sample Header Ad - 728x90

Combine Rows with indirect relation

1 vote
1 answer
154 views
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'
Asked by Bryan (11 rep)
Jun 25, 2020, 08:23 PM
Last activity: Jul 27, 2025, 11:04 PM