This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,906 Users
  • 2,269,775 Discussions


Is the following query possible with SQL Pivot?

odysam Member Posts: 11
edited Apr 24, 2014 2:12PM in SQL & PL/SQL

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

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, m.present
  from student s, meeting_attendance m
  where = m.student_id
  for class_meeting in ( select a.meeting_sequence
                         from class_meeting a, class b
                         where = 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
    TexasApexDeveloper Senior Oracle APEX Developer Los Alamos, NMMember Posts: 8,011 Gold Crown

    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
    Mike Kutz Member Posts: 6,317 Gold Crown

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

    ... )

    needs to be

    sum( present)
    ... )

    There are two types of PIVOT in Oracle

    • PIVOT

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


    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.


    Mike Kutz
  • Etbin
    Etbin SloveniaMember Posts: 8,968 Gold Crown

    Seems it is (at least in a way)


    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,,ma.meeting_id,nullif(ma.present,-1) present

              from meeting_attendance ma,

                   student s

             where ma.student_id =


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

    order by id

    NAMEMeeting 1Meeting 2Meeting 3



This discussion has been closed.