Home > PL/SQL > Result in one Line

Result in one Line

Friday, 29 May, 2009 Leave a comment Go to 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: , ,
  1. Friday, 29 May, 2009 at 2:18 pm
  2. Friday, 29 May, 2009 at 2:38 pm
  3. Monday, 18 January, 2010 at 10:17 am

    test

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: