Oracle 行列转换

　　说到行列转换，我们就不得不提到一个函数，它就是wmsys.wm_concat()。我们先来看一些例子，然后在进行详细介绍。

　一、wmsys.wm_concat()函数

   SQL> desc tab_a;
　　　　Name 　　 Type 　　　　 Nullable  　Default Comments
　　　　------- ------------ -------- ------- --------
　　　　COLUM_A VARCHAR2(10) Y
　　　　COLUM_B VARCHAR2(10) Y
　　SQL> select * from tab_a;
　　　　COLUM_A COLUM_B
　　　　---------- ----------
　　　　aa 1
　　　　bb 1
　　　　cc 2
　　　　dd 2
　　　　ee 3
　　　　ff 3
　　　　gg 3
　　　　hh 3
　　　　8 rows selected
　　SQL> select wmsys.wm_concat(COLUM_A) from tab_a;
　　　　WMSYS.WM_CONCAT(COLUM_A)
　　　　--------------------------------------------------------------------------------
　　　　aa,bb,cc,dd,ee,ff,gg,hh
　　SQL> select COLUM_B, wmsys.wm_concat(COLUM_A) from tab_a GROUP BY COLUM_B;
　　　　COLUM_B WMSYS.WM_CONCAT(COLUM_A)
　　　　---------- --------------------------------------------------------------------------------
　　　　1 aa,bb
　　　　2 cc,dd
　　　　3 ee,ff,gg,hh

 　　看到这个函数的强大之处了吧。利用这个函数我们可以将任何一张表的字段以行的形式进行展示，对于sqlldr生成控制文件脚本以及sqlldr脚本很有帮助，如以下SQL：

　　SQL> SELECT table_name, wmsys.wm_concat(a.column_name) COLIST　FROM user_tab_cols a GROUP BY a.table_name;
　　　　TABLE_NAME COLIST
　　　　------------------------------ --------------------------------------------------------------------------------
　　　　BONUS ENAME,JOB,COMM,SAL
　　　　DEPT  DEPTNO,DNAME,LOC
　　　　EMP   EMPNO,ENAME,JOB,SAL,HIREDATE,MGR,COMM,DEPTNO
　　　　SALGRADE GRADE,LOSAL,HISAL

二、LISTAGG()函数

　　在oracle11gR2版本中，有新增一个LISTAGG()内建函数，此函数作用与wmsys.wm_concat()函数功能类似，也是进行行里转换的。

　　LISTAGG函数的语法结构：
　　LISTAGG([,]) WITHIN GROUP (ORDER BY ) [OVER(PARTITION BY )]
　　LISTAGG()函数是聚合函数，因此在使用LISTAGG函数时，需要有聚合的列、with Group 关键字以及分组中的order by 子句。下边SQL的功能与wmsys.wm_concat()函数功能一样的效果。

　　SELECT t.table_name,listagg(t.column_name, ',') within GROUP(ORDER BY t.column_name) COLIST
　　   FROM user_tab_columns t
　　GROUP BY t.table_name;   --',' 是字段分隔符

三、connect by 与sys_connect_by_path()函数

　　使用connect by...start with递归的办法也能达到以上行里转换类似的效果。

　　connect by...start with 语法主要作用是找到根节点的树形子节点或者是找到叶子节点的根节点，是一种树形结构。

　　SELECT *
　　FROM table_name
　　[START WITH initial-condition ]
　　CONNECT BY [ nocycle ] recurse-condition
　　WHERE...;

　　在oracle9i中提出一个新的函数SYS_CONNECT_BY_PATH()配合connect by一起使用。此函数的作用是将父子关系以一定的方式进行展示。借助此函数可以将行里进行转换，相对也比较简单，不过需要理解透彻connect by 用法以及SYS_CONNECT_BY_PATH()作用。

SQL> SELECT MAX(substr(sys_connect_by_path(column_name, ','), 2)) AS cllist
　　  FROM (SELECT column_name, rownum rn
　　 　　　　FROM user_tab_columns
　　 　　　　WHERE table_name = 'TAB_A')
　　 START WITH rn = 1
   　CONNECT BY rn = rownum;
---SQL执行结果
　　CLLIST
　--------------------------------------------------------------------------------
　COLUM_A,COLUM_B

 

 　　关于connect by...start with语法还有量个常用的函数，一个是connect_by_root()：返回根节点；一个是connect_by_is_leaf返回是否是叶子节点。

　四、附注

　　　关于wmsys包含有其它函数，后续将把具体函数的功能以及用途补上。

　　　 (1) WM_CONTAINS()

 　　　(2) WM_EQUALS()

　　　 (3) WM_GREATERTHAN()

　　　 (4) WM_INTERSECTION()

　　　 (5) WM_LDIFF()

　　　 (6) WM_LESSTHAN()

　　　 (7) WM_MEETS()

　　　 (8) WM_OVERLAPS()

　　　 (9) WM_RDIFF()

　　关于wmsys包的其他函数可参考博客：http://ugvanxk.blogbus.com/logs/198628476.html

      ----------------------------------THE END-------------------------------------------