Re: SOLVED: Re: Getting all rows of sqlplus output on one line

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Thu, 5 Oct 2006 15:09:09 -0700 (PDT)
Message-ID: <20061005220909.93413.qmail@web52809.mail.yahoo.com>

Take care when you want the columns to be in order when using stragg():

select stragg(username) users
from dba_users
where username like 'SYS%'
order by username
/
USERS

SYSTEM,SYS,SYSMAN Correct-->

select stragg(username) users
from (

        select username
        from dba_users
        where username like 'SYS%'
        order by username

)

USERS

SYS,SYSMAN,SYSTEM

Rodd Holman <Rodd.Holman_at_gmail.com> wrote:

> Thanks to Christopher Boyle and AskTom for the Type
> and function.
> Here's what I ended up with after creating them.
>
> set heading off
> set feedback off
> set linesize 32000
> set pagesize 0
> Spool c:\temp\sitelist.txt
> select stragg(site_id) TEXT_OUTPUT
> from lgrsdss_data_set_sites
> where definition_id in ('nnn1','nnn2','nnn3')
> order by site_id
> /
> Spool off
> edit c:\temp\sitelist.txt
>
> This opens notepad on the user's desktop and let's
> him select the whole thing as one string for
> pasting.
>
> Thanks to all who replied.
>
> Rodd

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 05 2006 - 17:09:09 CDT