Is the following query possible with SQL Pivot?

odysam

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.

TexasApexDeveloper

NOT an APEX specific question, Please re-post to the SQL & PL/SQL support forum..

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Mike Kutz

The value that is displayed in the cell needs to be an aggregate.  eg sum(), count(), avg(), etc.

pivot(
present
... )

needs to be

pivot(
sum( present)
... )

There are two types of PIVOT in Oracle

• PIVOT
• PIVOT XML

PIVOT requires a static list of values so that the columns that are returned are static

eg

for class_meeting in ('Meeting 1', 'Meeting 2', 'Meeting 3')

PIVOT XML can use a dynamic list but the data is returned as XMLType which can't be used by APEX.

However, there is a "cheat"... transform the XML to XHTML.

See fac586's answer here: 

Only you can decide which method is best.

MK

Etbin

Seems it is (at least in a way)

with

student(id,name) as

(select 1,'Mary' from dual union all

select 2,'Steve' from dual union all

select 3,'John' from dual union all

select 4,'Etbin' from dual

),

class(id,subject) as

(select 1,'Computer science' from dual union all

select 2,'Applied mathematics' from dual

),

class_meeting(id,class_id,meeting_sequence_number) as

(select 1,1,1 from dual union all

select 2,1,2 from dual union all

select 3,1,3 from dual union all

select 4,2,1 from dual

),

meeting_attendance(id,student_id,meeting_id,present) as

(select 1,1,1,1 from dual union all

select 2,2,1,-1 from dual union all

select 3,3,1,1 from dual union all

select 4,1,2,-1 from dual union all

select 5,2,2,1 from dual union all

select 6,3,2,1 from dual union all

select 7,1,3,1 from dual union all

select 8,2,3,1 from dual union all

select 9,3,3,-1 from dual union all

select 10,4,4,-1 from dual

)

select name,"Meeting 1","Meeting 2","Meeting 3"

  from (select s.id,s.name,ma.meeting_id,nullif(ma.present,-1) present

          from meeting_attendance ma,

               student s

         where ma.student_id = s.id

       )

pivot (count(present) for meeting_id in (1 "Meeting 1",2 "Meeting 2",3 "Meeting 3"))

order by id

NAME	Meeting 1	Meeting 2	Meeting 3
Mary	1	0	1
Steve	0	1	1
John	1	1	0
Etbin	0	0	0

Regards

Etbin