Concatenate Columns in Different Rows
Question:
Consider the following table
create table t (
seq int,
seg varchar2(100)
);
Write a SQL to concatenate the segments of the different rows to return a single string, where the order is specified in the seq column
Answer:
This is a kind of recursive SQL… so, use CONNECT BY. New features from 9i / 10g are needed
select sys_connect_by_path(seg, ' > ')
from t
where connect_by_isleaf = 1
connect by seq - 1= prior seq
start with seq = 1
/
If there are 'holes' in seq, seq - 1= prior seq fails. You can use rownum to overcome this
select sys_connect_by_path(seg, ' > ')
from
(select rownum seq, seg
(select seg
from t
order by seq))
where connect_by_isleaf = 1
connect by seq - 1= prior seq
start with seq = 1
/
Another possible solution is to use a user-defined aggregate function. But this seems complicated… need to write a package
(0)