oracle10⾏转列,【转】oracle10g⾏列转换的写法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
oracle 时间转换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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论