将名称一起分组到同一模块组中

Question

我有一个PLSQL过程，通过它我从两个表中获取数据，并列出模块代码、姓名和正在接受模块的学生姓名。但是，我有一个问题，因为我现在的输出是这样的

BE1301 Business Analytics: Robert
CIST2013 Data Fundamentals: Henry
BE1301 Business Analytics: Betty
LS1101 Communication and skills: Aron

然而，我希望它是这样的

BE1301 Business Analytics: Robert, Betty
CIST2013 Data Fundamentals: Henry
LS1101 Communication and skills: Aron

下面是我的输出的过程select语句

create or replace procedure module groups is
modulegrp module.m#%type := -1;
 begin
  for cRow IN ( select m#,mname,studname,
                      from module left outer join student
                      on module.class = student.class
                      order by name DESC )
                      loop 
  if cRow.c_custkey is not null then
                     dbms_output.put_line(cRow.M# || cRow.mname || ':' | cRow.studname);
  end if;
  end loop;
  end;
  /

Answer 1

您可以在Oracle中使用group by和ListAgg聚合函数：

select m#,mname,LISTAGG(studname, ',') WITHIN GROUP (ORDER BY studname) as names 
from module 
left outer join student
    on module.class = student.class
group by m#,mname
order by name DESC