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

列转行

主要讨论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,MGRNO

3、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

下面是列转行的二个用法 1

with temp as (select account_no, user_party_id, data_hierarchy_id from t_cc_l2_employee

whereaccount_no is not null)

selectaccount_no, user_party_id from temp union all

selectaccount_no, data_hierarchy_id from temp 2

SELECT account_no, cn, cv FROM (

select distinct account_no, user_party_id, data_hierarchy_id from t_cc_l2_employee whereaccount_no is not null anduser_party_id is not null anddata_hierarchy_id is not null) MODEL

RETURN UPDATED ROWS

PARTITION 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: Non unique addressing in MODEL dimensions (问题出在被Model的结果集中的partition by对应的column有重复值)

2 ORA-25137 Data value out of range (将'yyyyyy' AS cv 中的'yyyyyy' 扩大几位就可能解决您的问题了)

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle列转行和行转列的几种用法在线全文阅读。