sys_connect_by_path 进行列连接

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