ITPub博客

首页 > 应用开发 > IT综合 > 行列转换的问题

行列转换的问题

原创 IT综合 作者:love2008 时间:2007-01-11 23:22:08 0 删除 编辑

行列转换的问题

[@more@]

我的测试数据:
LOVE2008>select * from test;

ID NAME
---------- --------------------
1 我
1 是
1 谁
2 我
2 是
2 love2008

6 rows selected.

这个是10G的处理方法:(增加了connect_by_isleaf,CONNECT_BY_ROOT,CONNECT_BY_ISCYCLE)
select replace(name,'/','') from (
select connect_by_isleaf ll,sys_connect_by_path(name,'/') name from (
select row_number()over(partition by id order by id) c_id,name,id,
row_number()over(partition by id order by id) -1 p_id
from test
) a
connect by prior c_id=p_id and prior id=id
start with p_id=0)
where ll=1

9i的处理方法:
select max(replace(name,'/','')) from (
select id,sys_connect_by_path(name,'/') name from (
select row_number()over(partition by id order by id) c_id,name,id,
row_number()over(partition by id order by id) -1 p_id
from test
) a
connect by prior c_id=p_id and prior id=id
start with p_id=0)
group by id

备注:connect_by_root的用法是(select connect_by_root 相应列 from ....),他返回的是
相应列的根行对应此列的值。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/728254/viewspace-889933/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DBMS_STANDARD包
请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    78
  • 访问量
    1116084