最近在論壇上，經常會看到關於分組後欄位拼接的問題，
大概是類似下列的情形：
SQL> select no,q from test
2 /

NO Q
---------- ------------------------------
001 n1
001 n2
001 n3
001 n4
001 n5
002 m1
003 t1
003 t2
003 t3
003 t4
003 t5
003 t6

12 rows selected

最後要得到類似於如下的結果：
001 n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6

[@more@]

通常大家都認為這類問題無法用一句SQL解決，本來我也這麼認為，可是今天無意中突然有了靈感，原來是可以這麼做的：
前幾天有人提到過sys_connect_by_path的用法，我想這裡是不是也能用到這個方法，如果能做到的話，不用函式或存貯過程也可以做到了；要用到sys_connect_by_path，首先要自己構建樹型的結構，並且樹的每個分支都是單根的，例如1-〉2-〉3-〉4，不會存在1-〉2，1-〉3的情況；
我是這麼構建樹，很簡單的，看下面的結果就會知道了：
SQL> select no,q,rn,lead(rn) over(partition by no order by rn) rn1
2 from (select no,q,row_number() over(order by no,q desc) rn from test)
3 /

NO Q RN RN1
---------- ------------------------------ ---------- ----------
001 n5 1 2
001 n4 2 3
001 n3 3 4
001 n2 4 5
001 n1 5
002 m1 6
003 t6 7 8
003 t5 8 9
003 t4 9 10
003 t3 10 11
003 t2 11 12
003 t1 12

12 rows selected

有了這個樹型的結構，接下來的事就好辦了，只要取出擁有全路徑的那個path，問題就解決了，先看no=‘001’的分組：
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = '001' and rn1 is null connect by rn1 = prior rn
SQL>
6 /

NO RESULT
---------- --------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001 ;n1;n2;n3
001 ;n1;n2;n3;n4
001 ;n1;n2;n3;n4;n5

上面結果的最後1條就是我們要得結果了
要得到每組的結果，可以下面這樣

原文地址
http://www.itpub.net/397706.html

程式碼:

select t.*,

       (

        select max(sys_connect_by_path(q,';')) result from 

               (select no,q,rn,lead(rn) over(partition by no order by rn) rn1 

               from (select no,q,row_number() over(order by no,q desc) rn from test)

               )

        start with no = t.no and rn1 is null connect by rn1 = prior rn

       ) value

from (select distinct no from test)

SQL>
10 /

NO VALUE
---------- --------------------------------------------------------------------------------
001 ;n1;n2;n3;n4;n5
002 ;m1
003 ;t1;t2;t3;t4;t5;t6

對上面結果稍加處理就可以了，希望對大家有幫助：）

稍微改進下：

程式碼:


select no,max(sys_connect_by_path(q,';')) result from 
(

  select no,q,rn,lead(rn) over(partition by no order by rn) rn1 

   from (

          select no,q,row_number() over(order by no,q desc) rn from test

        )

 )

 start with rn1 is null 

 connect by rn1 = prior rn

 group by no;

程式碼:


select no,max(sys_connect_by_path(q,';')) result from 
(

       select no,q,(row_number() over(order by no,q desc) + rank() over(order by no)) rn

       from test
)

 connect by rn-1 = prior rn

 group by no;

改進下演算法，少一層巢狀查詢，效率會好些:

呵呵，剛剛的演算法有點問題，下面的應該沒問題了

程式碼:


select no,max(sys_connect_by_path(q,';')) result 

from (select no,q,(row_number() over(order by no,q desc) + dense_rank() over(order by no)) rn, 

             max(q) over(partition by no) qs

      from test
)

 start with q = qs

 connect by rn-1 = prior rn

 group by no;

關於分組後欄位拼接的問題[行列轉換]『By duanzilin』

相關文章