行列转换的问题

行列转换的问题

[@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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/728254/viewspace-889933/