行转列、列转行（sys_connect_by_path，row_number() over，count(*) over）,wmsys.wm_concat

SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！
它一定要和connect by子句合用！
第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！
START WITH 代表你要开始遍历的的节点

CONNECT BY PRIOR 是标示父子关系的对应

 select deptno,
        ltrim(sys_connect_by_path(ename,','),',') emps
   from (
 select deptno,
        ename,
        row_number() over
                 (partition by deptno order by empno) rn,
        count(*) over
                 (partition by deptno) cnt
   from emp
        )
  where level = cnt
  start with rn = 1
 connect by prior deptno = deptno and prior rn = rn-1;

   DEPTNO EMPS
----------------------------------------------
       10 CLARK,KING,MILLER
       20 SMITH,JONES,SCOTT,ADAMS,FORD
       30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL> select deptno,wmsys.wm_concat(ename) from emp group by deptno;

    DEPTNO WMSYS.WM_CONCAT(ENAME)
---------- --------------------------------------------------------------------------------
        10 CLARK,MILLER,KING
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

select deptno,
       ename,
       row_number() over
                (partition by deptno order by empno) rn,
       count(*) over
                (partition by deptno) cnt
  from emp;

    DEPTNO ENAME              RN        CNT
---------- ---------- ---------- ----------
        10 CLARK               1          3
        10 KING                2          3
        10 MILLER              3          3
        20 SMITH               1          5
        20 JONES               2          5
        20 SCOTT               3          5
        20 ADAMS               4          5
        20 FORD                5          5
        30 ALLEN               1          6
        30 WARD                2          6
        30 MARTIN              3          6
        30 BLAKE               4          6
        30 TURNER              5          6
        30 JAMES               6          6

SQL> select ','||'7654,7698,7782,7788'||',' emps from dual;

EMPS
---------------------
,7654,7698,7782,7788,

select substr(emps,instr(emps,',',1,iter.pos)+1,4)   from (select ','||'7654,7698,7782,7788'||',' emps
          from dual) csv,
       (select rownum pos from emp) iter
 where iter.pos <=
 ((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1;

SUBSTR(E
--------
7654
7698
7782
7788

select substr(emps, instr(emps, ',', 1, 1) + 1, 4),
       substr(emps, instr(emps, ',', 1, 2) + 1, 4),
       substr(emps, instr(emps, ',', 1, 3) + 1, 4),
       substr(emps, instr(emps, ',', 1, 4) + 1, 4)
  from (select ',' || '7654,7698,7782,7788' || ',' emps from dual) csv;

SUBS SUBS SUBS SUBS
---- ---- ---- ----
7654 7698 7782 7788