oracle 行转列

1、wm_concat   返回是一个CLOB 类型，不过在经我使用，这货在性能上有问题。

select to_char(wm_concat(t.ename)), t.deptno from scott.emp t group by t.deptno;

CLARK,MILLER,KING10SMITH,FORD,ADAMS,SCOTT,JONES20ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD30

2、sys_connect_by_path 加 over  PARTITION BY  虽然用起来有点麻烦，但是确实很快。

select t1.deptno, substr(max(sys_connect_by_path(t1.ename, ',')), 2) enames
  from (select t.deptno,
               t.ename,
               row_number() over(PARTITION BY t.deptno order by t.deptno) rn
          from scott.emp t) t1
 START WITH t1.rn = 1
CONNECT BY t1.deptno = PRIOR t1.deptno
       AND t1.rn - 1 = PRIOR t1.rn
 GROUP BY t1.deptno;

30WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN20JONES,FORD,ADAMS,SMITH,SCOTT10CLARK,KING,MILLER