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

Is the following query possible with SQL Pivot?(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,
  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))
);

我想要对每个班级进行查询，其中有一列用于学生姓名，该班级的每个 class_meeting 一列以及每个班级会议的单元格将显示当前属性，如果学生在场，则该属性应为 1在那次会议上，如果学生没有参加那次会议，则为 0.这是一张来自excel的图片供参考:

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 picture from excel for reference:

是否可以制作这样的顶级报告?通过谷歌搜索，我想我必须使用 Pivot，但是我很难理解如何在这里使用它.这是我到目前为止的查询:

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

不过，我确定这还差得远.甚至可以通过一个查询来做到这一点，还是应该使用 pl sql htp 和 htf 包来创建一个 html 表?

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?

这里非常缺乏经验的 oracle 开发人员，因此非常感谢您的帮助.

Pretty inexperienced oracle developer here, so any help is very appreciated.

推荐答案

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

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

我使用过的数据:

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 programming
insert into meeting_attendance (id, student_id, meeting_id, present) values (2, 1, 2, 1); -- Tom was at meeting 20 about programming
insert into meeting_attendance (id, student_id, meeting_id, present) values (3, 1, 3, 0); -- Tom was NOT at meeting 10 about databases
insert into meeting_attendance (id, student_id, meeting_id, present) values (4, 1, 4, 0); -- Tom was NOT at meeting 20 about databases
insert into meeting_attendance (id, student_id, meeting_id, present) values (5, 2, 1, 0); -- Odysseas was NOT at meeting 10 about programming
insert into meeting_attendance (id, student_id, meeting_id, present) values (6, 2, 2, 1); -- Odysseas was at meeting 20 about programming
insert into meeting_attendance (id, student_id, meeting_id, present) values (7, 2, 3, 0); -- Odysseas was NOT at meeting 10 about databases
insert into meeting_attendance (id, student_id, meeting_id, present) values (8, 2, 4, 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
先阅读这些内容总是值得的.

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.

• https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238
• 动态旋转表 Oracle
• 动态 Oracle Pivot_In_Clause

一个经典的报表可以把一个返回sql语句的函数体作为return.交互式报告不能.就目前而言，IR 是不可能的，因为它过于依赖元数据.

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.

例如，在经典报告区域源中使用这些查询/plsql:

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

静态枢轴

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

• 使用查询特定的列名并验证查询

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

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.

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;

运行页面而不编辑报告！编辑和保存会重新生成元数据，这显然不是一种可行的方法.反正数据会变，不能每次都进去保存报表元数据.

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.

这篇关于SQL Pivot 是否可以执行以下查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！