Archive

Posts Tagged ‘sql’

Result in one Line

Friday, 29 May, 2009 3 comments
with t as (
select 'string1' str from dual union all
select 'string2' str from dual union all
select 'string3' str from dual union all
select 'string4' str from dual union all
select 'string5' str from dual union all
SELECT 'string6' str from dual
)
select '~'||strs||'~'  AS oneline from
( select ltrim(sys_connect_by_path(str, '~'), '~') as strs
from
( select str, lag(str) over (order by str) as prev_str
from t
)
start with prev_str is null
connect by prev_str = prior str
order by 1 desc
)
where rownum = 1;

Result set

SQL>
oneline
--------------------------------------------------
~string1~string2~string3~string4~string5~string6~

using

...
where
Instr('~string1~string2~string3~string4~string5~string6~', 
'~'||trim(rtrim(t.city))||'~')  <> 0
...
Advertisements
Categories: PL/SQL Tags: , ,