Oracle将一列值按照另一列分组拼接的方法讲解

怎么把一列值按照另一列分组拼成字符串。

比如下面两列：

sql> select deptno,ename from emp where deptno is not null;
        20 smith
        30 allen
        30 ward
        20 jones
        30 martin
        30 blake
        10 clark
        20 scott
        10 king
        30 turner
        20 adams
        30 james
        20 james
        10 miller

14 rows selected.

将ename根据deptno的值拼成字符串。

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

select max(sys_connect_by_path(ename, ',')) 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;

结果为：

,clark,king,miller,scott,turner,ward                       30
,clark,king,miller,scott,smith                             20
,clark,king,miller                                         10

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

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

    deptno n
---------- --------------------------------------------------
        10 clark,miller,king
        20 smith,james,adams,scott,jones
        30 allen,james,turner,blake,martin,ward