列转行
主要讨论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' 扩大几位就可能解决您的问题了)