Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Is the following query possible with SQL Pivot?

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.
Answers
-
NOT an APEX specific question, Please re-post to the SQL & PL/SQL support forum..
Thank you,
Tony Miller
LuvMuffin Software
Ruckersville, VA -
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.
Only you can decide which method is best.
MK
-
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