实现方法一： 用分析函数row_number() over进行分组然后用sys_connect_by_path进行拼接：

select max(substr(sys_connect_by_path(ename, ','),2)) name,deptno
  from (select ename, deptno, row_number() over(partition by deptno order by ename) ro
          from (select deptno,ename from emp where deptno is not null)
      ) newtab
start with newtab.ro = 1
connect by prior newtab.ro = newtab.ro - 1
group by deptno;

select max(substr(sys_connect_by_path(ename, ','),2)) name,deptno
  from (select ename, deptno, row_number() over(partition by deptno order by ename) ro
          from (select deptno,ename from emp where deptno is not null)
      ) newtab
start with newtab.ro = 1
connect by newtab.ro=rownum

group by deptno;

实现方法二：用函数wm_concat(列名)，该函数可以把列值以逗号分隔，并显示成一行：

select deptno,wm_concat(ename) n from emp where deptno is not null group by deptno;

实现方法三：用函数listagg(列名,分隔符)，为oracle11gr2版本新出函数，可实现组内排序，并自定义分隔符号：

select deptno,listagg(ename,',') n group (order by ename) from emp where deptno is not null group by deptno;