SQL Pivot 是否可以执行以下查询?

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.

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

• 使用通用列名(仅在运行时解析查询)

将源设置为这种类型将允许您使用更动态的方法.通过将报告的设置更改为这种类型的解析，apex 将仅在其元数据中生成一定数量的列，而不会直接与实际查询相关联.只会有带有COL1"、COL2"、COL3"、...
运行报告.工作正常.现在再次插入一些数据.

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.

begin
insert 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 youtube
end;

运行报告.工作正常.
然而，这里的扭结是列名.他们并不是那么有活力，有着丑陋的名字.您当然可以编辑列，但它们不是动态的.没有显示任何类或任何东西，也不能可靠地将它们的标题设置为 1.这又是有道理的:元数据在那里，但它是静态的.如果您对这种方法感到满意，它可能对您有用.
但是，您可以处理此问题.在报告的报告属性"中，您可以选择标题类型".它们都是静态的，当然期待PL/SQL"！在这里你可以编写一个函数体(或者只是调用一个函数)，它会返回列标题！

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;

第三方解决方案

• noreferr="nofollow">//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/
  在 APEX 中:虽然安装后动态枢轴更加简单，但在 apex 中的设置与您想要使用动态 SQL 时保持相同.使用具有通用列名称的经典报告.
  我不会在这里详细介绍.我没有安装这个包atm.很高兴拥有，但在这种情况下，它可能没有那么有用.它纯粹允许您以更简洁的方式编写动态枢轴，但在事物的顶点方面没有多大帮助.正如我在上面演示的，动态列和顶点报告的静态元数据是这里的限制因素.

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.

我自己以前选择使用 XML 关键字.我使用数据透视来确保所有行和列都有值，然后用 XMLTABLE 再次读出，然后创建一个 XMLTYPE 列，将其序列化为 CLOB.
这可能有点高级，但到目前为止我已经使用过几次这种技术，并且效果很好.它很快，前提是基础数据不是太大，而且它只是一个 sql 调用，所以不需要很多上下文切换.我也将它与 CUBE 数据一起使用，效果很好.
(注意:我在元素上添加的类与主题 1 中经典报告中使用的类相对应，简单的红色)

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;

在APEX中:好吧，既然已经构建了HTML，这只能是一个PLSQL区域，它调用包函数并使用HTP.PRN打印出来.

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.

(编辑)OTN 论坛上也有这篇文章，它在很大程度上执行相同的操作，但不生成标题等，而是使用顶点功能:OTN:矩阵报告

(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

或者，您可以选择走好 ol' plsql 路线.您可以从上面的动态 sql 中取出主体，对其进行循环，然后使用 htp.prn 调用生成一个表结构.放出标题，然后放出您想要的任何其他内容.为了获得良好的效果，请在与您正在使用的主题相对应的元素上添加类.

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.