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

---------------------------------------------------------------------------------------------------------------------

with 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 temp
union all
select account_no, data_hierarchy_id from temp

SELECT 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)
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' 扩大几位就可能解决您的问题了)