Let's say I have the following tables:
create table student(
id number not null,
name varchar2(80),
primary key(id)
);
create table class(
id number not null,
subject varchar2(80),
primary key(id)
);
create table class_meeting(
id number not null,
class_id number not null,
meeting_sequence number,
primary key(id),
foreign key(class_id) references class(id)
);
create table meeting_attendance(
id number not null,
student_id number not null,
meeting_id number not null,
present number not null,
primary key(id),
foreign key(student_id) references student(id),
foreign key(meeting_id) references class_meeting(id),
constraint meeting_attendance_uq unique(student_id, meeting_id),
constraint present_ck check(present in(0,1))
);
I want a query for each class, which has a column for the student name, one column for every class_meeting for this class and for every class meeting the cells would show the present attribute, which should be 1 if the student was present at that meeting and 0 if the student was absent in that meeting. Here is a table for reference
| Name | Meeting 1 | Meeting 2 | Meeting 3 |
|---|
| John | 1 | 1 | 0 |
| Mary | 1 | 0 | 1 |
| Steve | 0 | 1 | 1 |
Is it possible to make an apex report like that? From googling I figured I must use Pivot, however I'm having a hard time understanding how it could be used here. Here is the query I have so far:
select * from(
select s.name, m.present
from student s, meeting_attendance m
where s.id = m.student_id
)
pivot(
present
for class_meeting in ( select a.meeting_sequence
from class_meeting a, class b
where b.id = a.class_id )
)
However I'm sure it's way off. Is it even possible to do this with one query, or should I use pl sql htp and htf packages to create an html table?
Pretty inexperienced oracle developer here, so any help is very appreciated.