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,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的图片供参考：

是否有可能做出类似的顶级报告？
从谷歌上搜索我认为我必须使用Pivot,但是我很难理解它是如何在这里使用的.这是我到目前为止的查询：

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表？

相当缺乏经验的oracle开发人员,所以任何帮助都非常感谢.

解决方法

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

我使用的数据：

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,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",meeting) order by meeting) 
        ) headers
    FROM (SELECT DISTINCT meeting FROM src)
  ),html_src as (
  SELECT 
    xmlagg(
      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调用放出表结构.拿出标题,并推出你想要的任何其他内容.为了获得良好的效果,请在与您正在使用的主题相对应的元素上添加类.

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

解决方法

猜你在找的MsSQL相关文章