行转列

2010-1-3 今天给海鹏在测试库中处理了一个多根节点的结果集合；现在要是实现行列转换拼接成一个独立的行已经有两个方法了，哈哈哈哈哈哈哈，第二个方法可以实现

[@more@]

行转列08:45:47 SQL> select * from usr;NAME----------张三李四王五已选择3行。已用时间: 00: 00: 00.0008:45:57 SQL> Select Substr(Sys_Connect_By_Path(Name, ','), 2)08:46:51 2 From (Select Rownum Rid, Rownum + 1 Next_Rid, Name From Usr)08:46:51 3 Where Rid = (Select Count(*) From Usr)08:46:51 4 Start With Rid = 108:46:51 5 Connect By Prior Next_Rid = Rid;SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2)----------------------------------------------------------------------------张三,李四,王五已选择 1 行。已用时间: 00: 00: 00.00

oradbHome 发表于:2006.05.20 14:18 ::分类: ( Oracle Developer ) ::阅读:(6522次) :: 评论 (2) :: 引用 (0)

re: 行转列(Sys_Connect_By_Path) [回复]

专家您好：有无办法将下表
CELL RTF
25691 RTF-00-02
25691 RTF-00-03
48351 RTF-00-00
48351 RTF-00-01
48351 RTF-00-02排列成这种格式：
CELL RTF
25691 RTF-00-02,RTF-00-03
48351 RTF-00-00,RTF-00-01,RTF-00-02看了一下sys_connect_by_path函数，不知道用。

ineedask 评论于：2008.03.11 17:33

re: 行转列(Sys_Connect_By_Path) [回复]

select cell, ltrim(sys_connect_by_path(name,','),',') rtfs from(
select cell,rtf,
row_number() over(partition by cell order by rtf) rn,
count(*) over(partition by cell) cnt
from tabname
) where level = cnt
start with rn = 1
connect by prior cell = cell and prior rn + 1 = rn

LUCAS 评论于：2009.06.23 16:43

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

转载于:http://blog.itpub.net/450962/viewspace-1031083/