Oracle [sys_connect_by_path] 函數

標籤：des   io   ar   sp   on   art   bs   ad   as   

 

create table test
(
NO NUMBER,
VALUE VARCHAR2(100),
NAME VARCHAR2(100)
);

--------------------------------------
insert into test
select * from
(
select ‘1‘,‘a‘,‘測試1‘ from dual union all
select ‘1‘,‘b‘,‘測試2‘ from dual union all
select ‘1‘,‘c‘,‘測試3‘ from dual union all
select ‘1‘,‘d‘,‘測試4‘ from dual union all
select ‘2‘,‘e‘,‘測試5‘ from dual union all
select ‘4‘,‘f‘,‘測試6‘ from dual union all
select ‘4‘,‘g‘,‘測試7‘ from dual
);

 --------------------------------------

select No,
ltrim(max(sys_connect_by_path(Value, ‘;‘)), ‘;‘) as Value,
ltrim(max(sys_connect_by_path(Name, ‘;‘)), ‘;‘) as Name
from (

select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1

) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;


--------------------------------------
select No,
ltrim(max(sys_connect_by_path(Value, ‘;‘)), ‘;‘) as Value,
ltrim(max(sys_connect_by_path(Name, ‘;‘)), ‘;‘) as Name
from (

select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1

) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;

-----------------------------------------

-----------------------------------------

把C_MAIL表中的所有列名串起來：

select max(substr(sys_connect_by_path(column_name,‘,‘),2))
from (select column_name,rownum rn from user_tab_columns where table_name =‘C_MAIL‘)
start with rn=1 connect by rn=rownum ;

select sys_connect_by_path(column_name,‘,‘)
from (select column_name,rownum rn from user_tab_columns where table_name =‘C_MAIL‘)
start with rn=1 connect by rn=rownum ;

 

Oracle [sys_connect_by_path] 函數