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,rownum rn 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 deptno 8 / 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_id from t_cc_l2_employee where account_no is not null)select account_no, user_party_id from tempunion allselect account_no, data_hierarchy_id from temp2SELECT account_no, cn, cv FROM (select distinct account_no, user_party_id, data_hierarchy_id from t_cc_l2_employee where account_no is not null and user_party_id is not null and data_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(cn1 = c1,cn2 = c2,cv1 = user_party_id0,cv2 = data_hierarchy_id0)ORDER BY account_no,cn;- 注意点：model语法SQL经常会遇到二个问题1 ORA-32638: Non unique addressing in MODEL dimensions (问题出在被Model的结果集中的partiti