ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个简单的行列转换

一个简单的行列转换

原创 Linux操作系统 作者:kinglawrence 时间:2011-05-11 21:00:37 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/21158541/viewspace-695050/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ASM日志检查
下一篇: LOGMINER使用
请登录后发表评论 登录
全部评论

注册时间:2010-02-08

  • 博文量
    60
  • 访问量
    82665