Oracle列转行和行转列的几种用法.docx

列转行主要讨论sys_connect_by_path的使用方法。1、带层次关系SQL create table dept(deptno number,deptname varchar2(20),mgrno number);Table created.SQL insert into dept values(1,总公司,null);1 row created.SQL insert into dept values(2,浙江分公司,1);1 row created.SQL insert into dept values(3,杭州分公司,2);1 row created.SQL commit;Commit complete.SQL select max(substr(sys_connect_by_path(deptname,,),2)) from dept connect by prior deptno=mgrno;MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,,),2))--------------------------------------------------------------------------------总 公司,浙江分公司,杭州分公司2、行列转换如把一个表的所有列连成一行，用逗号分隔:SQL select max(substr(sys_connect_by_path(column_name,,),2))from (select column_name,rownumrn from user_tab_columns where table_name =DEPT)start with rn=1 connect by rn=rownum ;MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,,),2))--------------------------------------------------------------------------------DEPTNO,DEPTNAME,MGRNO3、ListAgg(Oracle 11g)SQL select deptno, 2 listagg(ename, ; ) 3 within group 4 (order by ename) enames 5 from emp 6 group by deptno 7 order by deptno8 / DEPTNO ENAMES--------- ------------------- 10 CLARK; KING; MILLER 20 ADAMS; FORD; JONES; SCOTT; SMITH 30 ALLEN; BLAKE; JAMES; MARTIN;TURNER; WARD下面是列转行的二个用法1with temp as (select account_no, user_party_id, data_hierarchy_idfrom t_cc_l2_employeewhereaccount_no is not null)selectaccount_no, user_party_id from tempunion allselectaccount_no, data_hierarchy_id from temp2SELECT account_no, cn, cv FROM (select distinct account_no, user_party_id, data_hierarchy_idfrom t_cc_l2_employeewhereaccount_no is not nullanduser_party_id is not nullanddata_hierarchy_id is not null)MODELRETURN UPDATED ROWSPARTITION BY (account_no)DIMENSION BY (0 AS n)MEASURES (xx AS cn,yyyyyy AS cv, user_party_id, data_hierarchy_id)RULES UPSERT ALL(cn[1] = c1,cn[2] = c2,cv[1] = user_party_id[0],cv[2] = data_hierarchy_id[0])ORDER BY account_no,cn;-- 注意点：model语法SQL经常会遇到二个问题1 ORA-32638: N