CODEWITHSUNDEEP
sqloracle-databaseplsql
Alan
Alan

Reputation: 9471

Mapping rows to columns in different tables

I am stumped as how to elegantly map data from rows into columns in other tables.

Input Table

FORM_SUBMISSION Table -

ID = The Form Identifier ENTRY_ID = The Question Identifier RESPONSE = Their Response

ID  ENTRY_ID   RESPONSE
4   24         John 
4   25         Doe
4   26         Male
4   32         NY
4   30         Life-Threatening
4   30         Other Serious
4   30         Hospitalization
4   28         Tylenol
4   31         I have a headache.

I need to map it to a couple tables PATIENT_INFO, PATIENT_OUTCOME

Output Tables

PATIENT_INFO

REPORT_ID    FIRST_NAME   LAST_NAME    STATE   GENDER    COMPLAINT
4            John         Doe          NY      Male      I have a headache.

PATIENT_OUTCOME

REPORT_ID    OTHER_SERIOUS_IND   LIFE_THREAT_IND    HOSPITAL_IND   DISABILITY_IND
4            Y                   Y                  Y              N

So there is no direct way to link the rows to the columns, Would it be possible to create a mapping based on the ENTRY_ID and column name? Though I know for the IND columns are based on all rows with ENTRY_ID = 30.

Upvotes: 0

Views: 96

Answers (1)

sstan
sstan

Reputation: 36513

It's possible to do with conditional aggregation. But, in the end, you need still need to figure out the rules to do the mappings correctly.

Here is some sample SQL based on my very basic understanding of how the mapping works. You'll probably need to tweak it further.

PATIENT_INFO

select id as report_id,
       max(case when entry_id = 24 then response end) as first_name,
       max(case when entry_id = 25 then response end) as last_name,
       max(case when entry_id = 32 then response end) as state,
       max(case when entry_id = 26 then response end) as gender,
       max(case when entry_id = 31 then response end) as complaint
  from form_submission
 group by id

PATIENT_OUTCOME

select id as report_id,
       nvl(max(case when entry_id = 30 and response = 'Other Serious' then 'Y' end), 'N') as OTHER_SERIOUS_IND,
       nvl(max(case when entry_id = 30 and response = 'Life-Threatening' then 'Y' end), 'N') as LIFE_THREAT_IND,
       nvl(max(case when entry_id = 30 and response = 'Hospitalization' then 'Y' end), 'N') as HOSPITAL_IND,
       nvl(max(case when entry_id = 30 and response = '??Disability??' then 'Y' end), 'N') as DISABILITY_IND
  from form_submission
 group by id

Upvotes: 1

Related Questions