Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Is the following query possible with SQL Pivot?

odysamApr 24 2014 — edited Apr 24 2014

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

NameMeeting 1Meeting 2Meeting 3
John110
Mary101
Steve011

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 22 2014
Added on Apr 24 2014
3 comments
1,722 views