Sql-server – Combine Rows with indirect relation

querysql servert-sql

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

select
  max(x.members_present),
  max(x.patient_category)
from
(
  <your query goes here>
) as x
group by
  x.clientvisit_id,
  x.client_id 

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