Oracle行列转换概要.docVIP

作者：NinGoo | 【转载须以超链接形式标明文章原始出处和作者信息】 假如有如下表，其中各个i值对应的行数是不定的SQL select * from t; I A D———- ———- ——————-1 b 2008-03-27 10:55:421 a 2008-03-27 10:55:461 d 2008-03-27 10:55:302 z 2008-03-27 10:55:552 t 2008-03-27 10:55:59 要获得如下结果，注意字符串需要按照D列的时间排序：1 d,b,a2 z,t 这是一个比较典型的行列转换，有好几种实现方法 1.自定义函数实现create or replace function my_concat(n number)return varchar2istype typ_cursor is ref cursor;v_cursor typ_cursor;v_temp varchar2(10);v_result varchar2(4000):= ”;v_sql varchar2(200);beginv_sql := ‘select a from t where i=’ || n ||’ order by d’;open v_cursor for v_sql;loopfetch v_cursor into v_temp;exit when v_cursor%notfound;v_result := v_result ||’,’ || v_temp;end loop;return substr(v_result,2);end; SQL select i,my_concat(i) from t group by i; I MY_CONCAT(I)———- ——————–1 d,b,a2 z,t 虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。 2.使用sys_connect_by_pathselect i,ltrim(max(sys_connect_by_path(a,’,)),’,) afrom(select i,a,d,min(d) over(partition by i) d_min,(row_number() over(order by i,d))+(dense_rank() over (order by i)) numidfrom t)start with d=d_min connect by numid-1=prior numidgroup by i; 从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候： 3.使用wm_sys.wm_concat 这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序 SQL select i,wmsys.wm_concat(a) from t group by i; I WMSYS.WM_CONCAT(A)———- ——————–1 b,a,d2 z,t SQL select i,wmsys.wm_concat(a)2 from3 (select * from t order by i,d)4 group by i; I WMSYS.WM_CONCAT(A)———- ——————–1 d,b,a2 z,t 执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。 不知道大家还有没有更加高效的实现方式，欢迎指教^_^ 其他一些方法，可以参考： Oracle 行列转换 总结? 行列转换包括以下六种情况：　　1. 列转行　　2. 行转列　　3. 多列转换成字符串　　4. 多行转换成字符串　　5. 字符串转换成多列　　6. 字符串转换成多行 首先声明，有些例子需要如下10g及以后才有的知识：　　A. 掌握model子句，　　B. 正则表达式　　C. 加强的层次查询 1、列转行CREATE TABLE t_col_row(　　ID INT,　　c1 VARCHAR2(10),　　c2 VARCHAR2(10),　　c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, v11, v21, v31);INSERT INTO t_col_row VALUES (2, v12, v22, NULL);