I'm using Oracle Apex 21.2 on a 21.c database. I have three tables that I use for a report of volunteers:
VOL_CONTACT
VOL_MINISTRY
VOL_CONTACT_MINISTRY
The VOL_CONTACT_MINISTRY table contains two foreign keys that equal the primary keys of VOL_CONTACT and VOL_MINISTRY. I can execute the following SQL, but it produces multiple rows per contact, depending on how many ministries they're assigned. Each row has only 1 "X" underneath the appropriate column. I'm trying to get one row per contact, with 1 to “n” X's underneath the columns where they're assigned to a given ministry. I suspect I need to use a Group By or perhaps an Aggregate clause, but I'm having a hard time figuring out how to make it work for columns that are generated by a Case statement. For Clarity, here's what the report looks like now:
And Here is what I'd like the report to look like:
Here is the code that produces the original report:
select vc.prim_key,
vc.last_name,
vc.first_name,
Case when vcm.ministry_fkey = 13
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "ELL",
Case when vcm.ministry_fkey = 14
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "Health",
Case when vcm.ministry_fkey = 15
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "Finance",
Case when vcm.ministry_fkey = 16
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "Hospitality",
Case when vcm.ministry_fkey = 17
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "Publicity",
Case when vcm.ministry_fkey = 18
and vcm.contact_fkey = vc.prim_key
Then 'X' else null
End as "Education",
vc.email_1,
vc.email_2
from VOL_CONTACT vc,
VOL_CONTACT_MINISTRY vcm,
VOL_MINISTRY vm
where
vc.status = 'Active'
and vm.ministry = 'Refugee'
and vcm.contact_fkey = vc.PRIM_KEY
and vcm.ministry_fkey = vm.PRIM_KEY
order by vc.last_name, vc.first_name;
Thanks for looking at this