I have a schema with a table for students:
create table student( id number not null, name varchar2, surname varchar2, primary key(id) );
a table for classes:
create table class( id number not null, subject varchar2, primary key(id) );
Each class is completed in a number of meetings so there is a table for that:
create table class_meeting( id number not null, class_id number not null, meeting_number number not null, primary key(id), foreign key(class_id) references class(id) );
Also there is a table which tracks attendance of students in class meetings:
create table attendance( id number not null, student_id number not null, class_meeting_id number not null, present number, primary key(id), foreign key(student_id) references student(id), foreign key(class_meeting_id) references class_meeting(id), constraint present_ck check present in(0, 1) );
I am trying to build a report for every class which shows a list of all students and there are checkbox columns for every class meeting.
The checkboxes are checked is the student was present in that meeting and unchecked if not.
I also want to save checkbox state with an on submit process.
Here is a table of what the report would look like (imagine 'x' is a ticked checkbox)
|Name||Surname||Meeting 1||Meeting 2||Meeting 3|
From googling around I figured I should use the PIVOT function, but I have a hard time understanding exactly how it works and how to adjust it to this particular case.
So far I have written this query:
select * from ( select student.name, student.surname from student, class where class.id = :PX_CLASS_ID ) pivot ( attendance.present for class_meeting.id in(select class_meeting.id from class_meeting, class where class_meeting.class_id = class.id) ) where attendance.student_id = student.id
But I'm pretty sure this is completely wrong, I am not even sure if what I'm trying to do is possible. Also I have no idea where the APEX_ITEM.CHECKBOX2 statement would go or how I would assing the G_Fxx variable.
I am an inexperienced oracle developer, so any suggestions for solving the above problem, or any direction to the right documentation, is very appreciated.