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

来源：互联网发布：区姓和欧姓知乎编辑：程序博客网时间：2024/04/30 20:58

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--------7654769877827788

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