Is the following query possible with SQL Pivot?

It took a while to answer, but I had to write this all up and test it!

Data I've worked with:

begin insert into student(id, name) values (1, 'Tom');insert into student(id, name) values (2, 'Odysseas');insert into class(id, subject) values (1, 'Programming');insert into class(id, subject) values (2, 'Databases');insert into class_meeting (id, class_id, meeting_sequence) values (1, 1, 10);insert into class_meeting (id, class_id, meeting_sequence) values (2, 1, 20);insert into class_meeting (id, class_id, meeting_sequence) values (3, 2, 10);insert into class_meeting (id, class_id, meeting_sequence) values (4, 2, 20);insert into meeting_attendance (id, student_id, meeting_id, present) values (1, 1, 1, 1); -- Tom was at meeting 10 about programminginsert into meeting_attendance (id, student_id, meeting_id, present) values (2, 1, 2, 1); -- Tom was at meeting 20 about programminginsert into meeting_attendance (id, student_id, meeting_id, present) values (3, 1, 3, 0); -- Tom was NOT at meeting 10 about databasesinsert into meeting_attendance (id, student_id, meeting_id, present) values (4, 1, 4, 0); -- Tom was NOT at meeting 20 about databasesinsert into meeting_attendance (id, student_id, meeting_id, present) values (5, 2, 1, 0); -- Odysseas was NOT at meeting 10 about programminginsert into meeting_attendance (id, student_id, meeting_id, present) values (6, 2, 2, 1); -- Odysseas was at meeting 20 about programminginsert into meeting_attendance (id, student_id, meeting_id, present) values (7, 2, 3, 0); -- Odysseas was NOT at meeting 10 about databasesinsert into meeting_attendance (id, student_id, meeting_id, present) values (8, 2, 4, 1); -- Odysseas was at meeting 20 about databasesend;

PIVOT , as it stands right now, does not allow a dynamic number of columns in a simple way. It only allows this with the XML keyword, resulting in an xmltype column. Here are some excellent docs. http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
It always pays off to read those first.

How to, then?
You'll literally find tons of questions about the same thing once you start searching.

Dynamic SQL

• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238
• Dynamically pivoting a table Oracle
• Dynamic Oracle Pivot_In_Clause

A classic report can take a function body returning a sql statement as return. An interactive report can not. As it stands, an IR is out of the question as it is too metadata dependent.

For example, with these queries/plsql in a classic report region source:

static pivot

select *from (select s.name as student_name, m.present present, cm.meeting_sequence||'-'|| c.subject meetingfrom student sjoin meeting_attendance mon s.id = m.student_idjoin class_meeting cmon cm.id = m.meeting_idjoin class con c.id = cm.class_id)pivot ( max(present) for meeting in ('10-Databases' as "10-DB", '20-Databases' as "20-DB", '10-Programming' as "10-PRM", '20-Programming' as "20-PRM") );-- ResultsSTUDENT_NAME '10-Databases' 20-DB 10-PRM 20-PRMTom          0              0     1      1Odysseas     0              1     0      1

function body returning statement

DECLARE  l_pivot_cols VARCHAR2(4000);  l_pivot_qry VARCHAR2(4000);BEGIN  SELECT ''''||listagg(cm.meeting_sequence||'-'||c.subject, ''',''') within group(order by 1)||''''    INTO l_pivot_cols    FROM class_meeting cm    JOIN "CLASS" c      ON c.id = cm.class_id;  l_pivot_qry :=         'select * from ( '     || 'select s.name as student_name, m.present present, cm.meeting_sequence||''-''||c.subject meeting '     || 'from student s '     || 'join meeting_attendance m '     || 'on s.id = m.student_id '     || 'join class_meeting cm '     || 'on cm.id = m.meeting_id '     || 'join class c '     || 'on c.id = cm.class_id '     || ') '     || 'pivot ( max(present) for meeting in ('||l_pivot_cols||') )' ;  RETURN l_pivot_qry;END;

Take note however of the settings in the region source.

• Use Query-Specific Column Names and Validate Query

This is the standard setting. It will parse your query and then store the columns found in the query in the report metadata. If you go ahead and create a report with the above plsql code, you can see that apex has parsed the query and has assigned the correct columns. What is wrong with this approach is that that metadata is static. The report's metadata is not refreshed every time the report is being ran.
This can be proven quite simply by adding another class to the data.

begininsert into class(id, subject) values (3, 'Watch YouTube');insert into class_meeting (id, class_id, meeting_sequence) values (5, 3, 10);insert into meeting_attendance (id, student_id, meeting_id, present) values (10, 1, 5, 1); -- Tom was at meeting 10 about watching youtubeend;

Run the page without editing the report! Editing and saving will regenerate the metadata, which is clearly not a viable method. The data will change anyway, and you cannot go in and save the report metadata every time.

--cleanupbegindelete from class where id = 3;delete from class_meeting where id = 5;delete from meeting_attendance where id = 10;end;

• Use Generic Column Names (parse query at runtime only)

Setting the source to this type will allow you to use a more dynamic approach. By changing the settings of the report to this type of parsing, apex will just generate an amount of columns in its metadata without being directly associated with the actual query. There'll just be columns with 'COL1', 'COL2', 'COL3',...
Run the report. Works fine. Now insert some data again.

begininsert into class(id, subject) values (3, 'Watch YouTube');insert into class_meeting (id, class_id, meeting_sequence) values (5, 3, 10);insert into meeting_attendance (id, student_id, meeting_id, present) values (10, 1, 5, 1); -- Tom was at meeting 10 about watching youtubeend;

Run the report. Works fine.
However, the kink here are the column names. They're not really all that dynamic, with their ugly names. You can edit the columns, surely, but they're not dynamic. There is no class being displayed or anything, nor can you reliably set their headers to one. Again this makes sense: the metadata is there, but it is static. It could work for you if you're happy with this approach.
You can however deal with this. In the "Report Attributes" of the report, you can select a "Headings Type". They're all static, expect for "PL/SQL" of course! Here you can write a function body (or just call a function) which'll return the column headers!

DECLARE  l_return VARCHAR2(400);BEGIN  SELECT listagg(cm.meeting_sequence||'-'||c.subject, ':') within group(order by 1)    INTO l_return    FROM class_meeting cm    JOIN "CLASS" c      ON c.id = cm.class_id;  RETURN l_return;END;

Third party solution

• https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4843682300346852395#5394721000346803830
• https://stackoverflow.com/a/16702401/814048
• http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
  In APEX: though the dynamic pivot is more straightforward after installing, the setup in apex remains the same as if you'd want to use dynamic SQL. Use a classic report with generic column names.
  I'm not going to go into much detail here. I don't have this package installed atm. It's nice to have, but in this scenario it may not be that helpful. It purely allows you to write a dynamic pivot in a more concise way, but doesn't help much on the apex side of things. As I've demonstrated above, the dynamic columns and the static metadata of the apex reports are the limiting factor here.

Use XML

I myself have opted to use the XML keyword before. I use pivot to make sure I have values for all rows and columns, then read it out again with XMLTABLE, and then creating one XMLTYPE column, serializing it to a CLOB.
This may be a bit advanced, but it's a technique I've used a couple of times so far, with good results. It's fast, provided the base data is not too big, and it's just one sql call, so not a lot of context switches. I've used it with CUBE'd data aswell, and it works great.
(note: the classes I've added on the elements correspond with classes used on classic reports in theme 1, simple red)

DECLARE  l_return CLOB;BEGIN  -- Subqueries:  -- SRC  -- source data query  -- SRC_PIVOT  -- pivoted source data with XML clause to allow variable columns.   -- Mainly used for convenience because pivot fills in 'gaps' in the data.  -- an example would be that 'Odysseas' does not have a relevant record for the 'Watch Youtube' class  -- PIVOT_HTML  -- Pulls the data from the pivot xml into columns again, and collates the data  -- together with xmlelments.  -- HTML_HEADERS  -- Creates a row with just header elements based on the source data  -- HTML_SRC  -- Creates row elements with the student name and the collated data from pivot_html  -- Finally:  -- serializes the xmltype column for easier-on-the-eye markup  WITH src AS (    SELECT s.name as student_name, m.present present, cm.meeting_sequence||'-'||c.subject meeting      FROM student s      JOIN meeting_attendance m        ON s.id = m.student_id      JOIN class_meeting cm        ON cm.id = m.meeting_id      JOIN class c        ON c.id = cm.class_id   ),  src_pivot AS (  SELECT student_name, meeting_xml    FROM src pivot xml(MAX(NVL(present, 0)) AS is_present_max for (meeting) IN (SELECT distinct meeting FROM src) )  ),  pivot_html AS (  SELECT student_name       , xmlagg(           xmlelement("td", xmlattributes('data' as "class"), is_present_max)           ORDER BY meeting         ) is_present_html    FROM src_pivot       , xmltable('PivotSet/item'           passing meeting_xml           COLUMNS "MEETING" VARCHAR2(400) PATH 'column[@name="MEETING"]'                 , "IS_PRESENT_MAX" NUMBER  PATH 'column[@name="IS_PRESENT_MAX"]')   GROUP BY (student_name)  ),  html_headers AS (  SELECT xmlelement("tr",           xmlelement("th", xmlattributes('header' as "class"), 'Student Name')        , xmlagg(xmlelement("th", xmlattributes('header' as "class"), meeting) order by meeting)         ) headers    FROM (SELECT DISTINCT meeting FROM src)  ),  html_src as (  SELECT     xmlagg(      xmlelement("tr",           xmlelement("td", xmlattributes('data' as "class"), student_name)        , ah.is_present_html      )    ) data    FROM pivot_html ah  )  SELECT     xmlserialize( content       xmlelement("table"        , xmlattributes('report-standard' as "class", '0' as "cellpadding", '0' as "cellspacing", '0' as "border")        , xmlelement("thead", headers )        , xmlelement("tbody", data )      )      AS CLOB INDENT SIZE = 2    )    INTO l_return    FROM html_headers, html_src ;  htp.prn(l_return);END;

In APEX: well, since the HTML has been constructed, this can only be a PLSQL region which calls the package function and prints it using HTP.PRN.

(edit) There's also this post on the OTN forum which does the same in a large part, but does not generate headings etc, rather using the apex functionalities:OTN: Matrix report

PLSQL

Alternatively, you can just opt to go the good ol' plsql route. You could take the body from the dynamic sql above, loop over it, and put out a table structure by using htp.prn calls. Put out headers, and put out whatever else you want. For good effect, add classes on the elements which correspond with the theme you're using.

Disclaimer: I don't know apex specifically.

Here's a correct pivot query, assuming the class you want has an ID = 1, and that the meeting_id's for that class are 1,2,3.

select * from(  select s.name, a.present,m.id meeting_id  from student s, meeting_attendance a, class_meeting m, class c  where s.id = a.student_id    and m.id = a.meeting_id    and c.id = m.class_id     and c.id = 1)pivot(      sum(present)      for meeting_id in(1,2,3));

I don't believe you can use a sub-query to return the values for the "for in" of the pivot.