oracle10行转列,【转】oracle 10g 行列转换的写法

11g中有更好的解决方式,有个pivot函数

1

假如有如下表，其中各个i值对应的行数是不定的SQL> select * from t;

I A D

———- ———- ——————-

1 b 2008-03-27 10:55:42

1 a 2008-03-27 10:55:46

1 d 2008-03-27 10:55:30

2 z 2008-03-27 10:55:55

2 t 2008-03-27 10:55:59

要获得如下结果，注意字符串需要按照D列的时间排序：1 d,b,a

2 z,t

这是一个比较典型的行列转换，有好几种实现方法

1.自定义函数实现create or replace function my_concat(n number)

return varchar2

is

type typ_cursor is ref cursor;

v_cursor typ_cursor;

v_temp varchar2(10);

v_result varchar2(4000):= ”;

v_sql varchar2(200);

begin

v_sql := ‘select a from t where i=’ || n ||’ order by d’;

open v_cursor for v_sql;

loop

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

2 z,t

虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。

2.使用sys_connect_by_pathselect i,ltrim(max(sys_connect_by_path(a,’,')),’,') a

from

(

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)) numid

from t

)

start with d=d_min connect by numid-1=prior numid

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

2 z,t

SQL> select i,wmsys.wm_concat(a)

2 from

3 (select * from t order by i,d)

4 group by i;

I WMSYS.WM_CONCAT(A)

———- ——————–

1 d,b,a

2 z,t

执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。

不知道大家还有没有更加高效的实现方式，欢迎指教^_^

其他一些方法，可以参考：

源表：NUM  DR_ID    PE_ID    SEQ_NUM   DOB_DATE   NAME

10         10         10         10           07-11-01     Wang

10         11         12         13           08-02-09      Li

10         12         13         14           08-02-09     Qian

11         15         16         17           08-08-27      Du

11         22         23         45           08-05-19      Dong

11         33         55         88            07-11-01     Xia查询结果；num, dr1_dob_dt, dr1_name, dr2_dob_dt, dr2_name, dr3_dob_dt, dr3_name

10      07-11-01      Wang        08-02-09       Li               08-02-09      Qian

11      08-08-27       Du            08-05-19       dong         07-11-01       Xia

12     01_11_01    ZHAO也就是要将num相同的DOB_DT, NAME查出来放在同一行script.:

create table driver (num number, dr_id number, pe_id, number, seq_num number, dob_date date, name varchar2(10));

insert into driver values (10, 10, 10, 10, to_date('07_11_01', 'YY_MM_DD'), 'WANG');

insert into driver values (10, 11, 12, 13, to_date('08_02_09', 'YY_MM_DD'), 'lI');

insert into driver values (10, 12, 13, 14, to_date('06_12_01', 'YY_MM_DD'), 'QIANG');

insert into driver values (11, 15, 16, 17, to_date('07_11_01', 'YY_MM_DD'), 'DONG');

insert into driver values (11, 18, 19, 10, to_date('02_11_01', 'YY_MM_DD'), 'DU');

insert into driver values (11, 20, 21, 23, to_date('05_11_01', 'YY_MM_DD'), 'XIA');

insert into driver values (12, 14, 33, 34, to_date('01_11_01', 'YY_MM_DD'), 'ZHAO');

SQL:

其实MAX(decode(rn, 1, dob_date, NULL))跟MAX(decode(rn, 1, dob_date))是一样的，根据Oracle文档:

The DECODE function is allowed in SQL but not PL/SQL statements. A DECODE function compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.

SQL> SELECT num,

2  MAX(decode(rn, 1, dob_date, NULL)) dob_date1,

3  MAX(decode(rn, 1, name, NULL)) name1,

4  MAX(decode(rn, 2, dob_date, NULL)) dob_date2,

5  MAX(decode(rn,2, name, NULL)) name2,

6  MAX(decode(rn, 3, dob_date, NULL)) dob_date3,

7  MAX(decode(rn, 3, name, NULL)) name3

8  FROM (SELECT num,

9  dr_id,pe_id,seq_num,dob_date,name,

10  row_number() over(PARTITION BY num ORDER BY dr_id) AS rn

11  FROM driver) t

12  GROUP BY num

13  ORDER BY 1;

NUM DOB_DATE1 NAME1 DOB_DATE2 NAME2 DOB_DATE3 NAME3

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

10 2007/11/1 WANG 2008/2/9 lI         2006/12/1 QIANG

11 2007/11/1 DONG  2002/11/1 DU       2005/11/1 XIA

12 2001/11/1 ZHAO