# 关于分组后字段拼接的问题[行列转换]『By duanzilin』

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

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

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
)
)
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
)
connect by rn-1 = prior rn
group by no;``````

• 博文量
223
• 访问量
2840670