Forum Stats

  • 3,872,068 Users
  • 2,266,376 Discussions
  • 7,911,043 Comments

Discussions

Is the following query possible with SQL Pivot?

odysam
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
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.

Tagged:

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,990 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

    TexasApexDeveloper
  • Mike Kutz
    Mike Kutz Member Posts: 6,251 Gold Crown

    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

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

    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

    NAMEMeeting 1Meeting 2Meeting 3
    Mary101
    Steve011
    John110
    Etbin000

    Regards

    Etbin

    Etbin
This discussion has been closed.