SQL> create table shujukuai(id varchar2(10),name varchar2(20));

Table created.

SQL> insert into shujukuai values(1,'shujukuai');

1 row created.

SQL> insert into shujukuai values(1,'xshujukuai');

1 row created.

SQL> insert into shujukuai values(1,'xxshujukuai');

1 row created.

SQL> insert into shujukuai values(2,'x');

1 row created.

SQL> insert into shujukuai values(2,'xx');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from shujukuai
2 ;

ID         NAME
---------- --------------------
1          shujukuai
1          xshujukuai
1          xxshujukuai
2          x
2          xx

SQL> select id,name,row_number() over(order by name) rn
2 from shujukuai;

ID         NAME                         RN
---------- -------------------- ----------
1          shujukuai                     1
2          x                             2
1          xshujukuai                    3
2          xx                            4
1          xxshujukuai                   5

SQL> select id,name,rn,lead(rn) over(partition by id order by rn) rn1
2 from (select id,name,row_number() over(order by name) rn from shujukuai);

ID         NAME                         RN        RN1
---------- -------------------- ---------- ----------
1          shujukuai                     1          3
1          xshujukuai                    3          5
1          xxshujukuai                   5
2          x                             2          4
2          xx                            4

SQL> set linesize 180
SQL> col name for a30
SQL> col name for a40
SQL> select id,substr(max(sys_connect_by_path(name,';')),2) name
2 from (
select id,name,rn,lead(rn) over(partition by id order by rn) rn1
from (select id,name,row_number() over(order by name) rn from shujukuai)
) start with rn1 is null connect by rn1=prior rn group by id
6 ;

ID         NAME
---------- ----------------------------------------
1          xxshujukuai;xshujukuai;shujukuai
2          xx;x

ITPub博客

一个简单的行列转换

最新文章