1、建立表並插入資料
SQL> create table test(a number,b varchar2(20));
SQL> select * from test;
A B
---------- --------------------
1 a
1 b
1 c
1 d
1 e
2 a
2 b
3 a
3 v
3 d
10 rows selected
查看合并列結果
select a,max(sys_connect_by_path(b,';')) result
from
(select a,b,
(row_number() over(order by a,b desc)
--row_number()-1 over(order by a,b desc) rn1
+dense_rank() over(order by a)) rn,
max(b) over(partition by a) bs
from test)
start with b=bs
connect by rn-1 = prior rn
group by a;
結果如下：
A RESULT
---------- --------------------------------------------------------------------------------
1 ;e;d;c;b;a
2 ;b;a
3 ;v;d;a
或者
select a,max(sys_connect_by_path(b,';')) result
from
(select a,b,
row_number() over(order by a,b desc) rn,
row_number() over(order by a,b desc)-1 rn1,
--+dense_rank() over(order by a)) rn,
max(b) over(partition by a) bs
from test)
start with b=bs
connect by rn1 = prior rn and a = prior a
group by a;注釋：
select a,b,rn,lead(rn) over(partition by a order by rn) rn1
from
(select a,b,row_number() over(order by a,b desc) rn from test)
結果如下：
A B RN RN1
---------- -------------------- ---------- ----------
1 e 1 2
1 d 2 3
1 c 3 4
1 b 4 5
1 a 5
2 b 6 7
2 a 7
3 v 8 9
3 d 9 10
3 a 10
10 rows selected
select a ,sys_connect_by_path(b,';') result from
(select a,b,rn,lead(rn) over(partition by a order by rn) rn1
from
(select a,b,row_number() over(order by a,b desc) rn from test))
start with a=1 and rn1 is null connect by rn1=prior rn;
結果如下：
A RESULT
---------- --------------------------------------------------------------------------------
1 ;a
1 ;a;b
1 ;a;b;c
1 ;a;b;c;d
1 ;a;b;c;d;e
體會另見http://www.itpub.net/823705.html
