SQL Pivot可以进行以下查询吗？

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,@R_878_1674@),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,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))
);

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 )
)

花了一段时间才回答,但我不得不写下这一切并测试它！

我使用的数据：

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,meeTing_sequencE) values (2,20);
insert into class_meeTing (id,meeTing_sequencE) values (3,2,meeTing_sequencE) values (4,20);
insert into meeTing_attendance (id,student_id,meeTing_id,present) values (1,1); -- Tom was at meeTing 10 about progrAMMing
insert into meeTing_attendance (id,present) values (2,1); -- Tom was at meeTing 20 about progrAMMing
insert into meeTing_attendance (id,present) values (3,3,0); -- Tom was NOT at meeTing 10 about databases
insert into meeTing_attendance (id,present) values (4,4,0); -- Tom was NOT at meeTing 20 about databases
insert into meeTing_attendance (id,present) values (5,0); -- Odysseas was NOT at meeTing 10 about progrAMMing
insert into meeTing_attendance (id,present) values (6,1); -- Odysseas was at meeTing 20 about progrAMMing
insert into meeTing_attendance (id,present) values (7,0); -- Odysseas was NOT at meeTing 10 about databases
insert into meeTing_attendance (id,present) values (8,1); -- Odysseas was at meeTing 20 about databases
end;

PIVOT,就像现在一样,不允许以简单的方式使用动态数量的列.它只允许使用XML关键字,从而生成xmltype列.
这里有一些优秀的文档. http://www.Oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
首先阅读这些内容总是值得的.

那怎么样？
一旦你开始搜索,你会发现很多关于同一件事的问题.

动态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

经典报告可以将函数体返回sql语句作为返回.交互式报告不能.就目前而言,IR是不可能的,因为它太依赖于元数据.

例如,在经典报表区域源中使用这些查询/ plsql：

静态枢轴

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 ('10-Databases' as "10-DB",'20-Databases' as "20-DB",'10-ProgrAMMing' as "10-PRM",'20-ProgrAMMing' as "20-PRM") );

-- Results
stuDENt_name '10-Databases' 20-Db 10-PRM 20-PRM
Tom          0              0     1      1
Odysseas     0              1     0      1

函数体返回语句

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,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;

但请注意区域来源中的设置.

>使用特定于查询的列名称和验证查询

这是标准设置.它将解析您的查询,然后将查询中找到的列存储在报告元数据中.如果继续使用上面的plsql代码创建报告,您可以看到apex已解析查询并分配了正确的列.这种方法的错误在于元数据是静态的.每次运行报表时都不会刷新报表的元数据.
这可以通过向数据添加另一个类来简单地证明.

begin
insert into class(id,subject) values (3,'Watch YouTube');
insert into class_meeTing (id,meeTing_sequencE) values (5,10);
insert into meeTing_attendance (id,present) values (10,5,1); -- Tom was at meeTing 10 about watching youtube
end;

在不编辑报告的情况下运行页面！编辑和保存将重新生成元数据,这显然不是一种可行的方法.无论如何,数据都会发生变化,您无法每次都进入并保存报告元数据.

--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′,……
运行报告.工作良好.现在再次插入一些数据.

begin
insert into class(id,1); -- Tom was at meeTing 10 about watching youtube
end;

运行报告.工作良好.
但是,这里的问题是列名.他们丑陋的名字并不是那么有活力.您当然可以编辑列,但它们不是动态的.没有显示任何类或任何类,也不能可靠地将其标题设置为一个.这也是有道理的：元数据存在,但它是静态的.如果您对这种方法感到满意,它可能对您有用.
但是你可以处理这个问题.在报告的“报告属性”中,您可以选择“标题类型”.他们都是静态的,当然期待“PL / sql”！在这里你可以编写一个函数体(或者只是调用一个函数)来返回列标题！

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;

第三方解决方案

> 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/
在APEX中：虽然安装后动态数据透视更直接,但是顶点中的设置仍然与您想要使用动态sql一样.使用具有通用列名称的经典报表.
我不打算在这里详细介绍.我没有安装此软件包.很高兴有,但在这种情况下它可能没那么有用.它纯粹允许您以更简洁的方式编写动态数据透视图,但对事物的顶点方面没有多大帮助.如上所述,顶点报告的动态列和静态元数据是限制因素.

使用XML

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

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,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,@R_761_6353@(
           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'),@R_761_6353@(xmlelement("th",meeTing) order by meeTing) 
        ) headers
    FROM (SELECT disTinCT meeTing FROM srC)
  ),html_src as (
  SELECT 
    @R_761_6353@(
      xmlelement("tr",xmlelement("td",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打印它.

(编辑)在OTN论坛上也有这篇文章在很大程度上做了同样的事情,但没有生成标题等,而是使用顶点功能：
OTN: Matrix report

PLsql

或者,你可以选择去好的’plsql路线.您可以从上面的动态sql获取正文,循环它,并通过使用htp.prn调用放出表结构.拿出标题,并推出你想要的任何其他内容.为了获得良好的效果,请在与您正在使用的主题相对应的元素上添加类.