Oracle列转行 sys_connect_by_path
2010-04-16 09:58:38| 分类：
编程
| 标签：
|举报
|字号大中小 订阅
最近在论坛上，经常会看到关于分组后字段拼接的问题，
大概是类似下列的情形：
select no,q from test
NO
Q
---------- ------------------------------
001 n1
001
n2
001
n3
001 n4
001 n5
002
m1
003
t1
003 t2
003 t3
003
t4
003
t5
003 t6
12 rows selected
最后要得到类似于如下的结果：
001
n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6
通常大家都认为这类问题无法用一句SQL解决，本来我也这么认为，可是今天无意中突然有了灵感，原来是可以这么做的：
前几天有人提到过sys_connect_by_path的用法，我想这里是不是也能用到这个方法，如果能做到的话，不用函数或存贮过程也可以做到了；要用到sys_connect_by_path，首先要自己构建树型的结构，并且树的每个分支都是单根的，例如1-〉2-〉3-〉4，不会存在1-〉2，1-〉3的情况；
我是这么构建树，很简单的，看下面的结果就会知道了：
SQL> select no,q,rn,lead(rn) over(partition by no
order by rn) rn1
from
(select no,q,row_number() over(order by no,q
desc) rn from test)
NO Q
RN
RN1
----------
------------------------------ ---------- ----------
001 n5
1
2
001 n4
2
3
001 n3
3
4
001
n2
4 5
001
n1
5
002
m1
6
003 t6
7
8
003
t5
8 9
003
t4
9 10
003 t3
10 11
003 t2
11
12
003 t1
12
12 rows selected
有了这个树型的结构，接下来的事就好办了，只要取出拥有全路径的那个path，问题就解决了，先看no=‘001’的分组：
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn)
over(partition by no order by rn) rn1
from (select
no,q,row_number() over(order by no,q desc) rn
from test)
)
start with no = '001' and rn1 is
null connect by rn1 = prior rn
SQL>
6 /
NO RESULT
----------
--------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001
;n1;n2;n3
001
;n1;n2;n3;n4
001
;n1;n2;n3;n4;n5
上面结果的最后1条就是我们要得结果了
要得到每组的结果，可以下面这样
select t.*,
(
select max(sys_connect_by_path(q,';')) result
from
(select no,q,rn,lead(rn) over(partition by no
order by rn) rn1
from (select no,q,row_number()
over(order by no,q desc) rn from test)
)
start with no
= t.no and rn1 is null connect by
rn1 = prior rn
) value
from (select distinct no
from test) t
SQL>
10 /
NO
VALUE
----------
--------------------------------------------------------------------------------
001 ;n1;n2;n3;n4;n5
002 ;m1
003
;t1;t2;t3;t4;t5;t6
对上面结果稍加处理就可以了，希望对大家有帮助：）
评论这张
转发至微博
转发至微博
关闭
玩LOFTER，免费冲印20张照片，人人有奖！
我要抢>
<#--最新日志，群博日志-->
<#--推荐日志-->
<#--引用记录-->
<#--博主推荐-->
<#--随机阅读-->
<#--首页推荐-->
<#--历史上的今天-->
<#--被推荐日志-->
<#--上一篇，下一篇-->
<#-- 热度 -->
<#-- 网易新闻广告 -->
<#--右边模块结构-->
<#--评论模块结构-->
<#--引用模块结构-->
<#--博主发起的投票-->
评论