Re: Suppressing newline during a SELECT returning multiple rows

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0402051017.3850af8a_at_posting.google.com>...
> srabbani_at_yahoo.com (Shuaib) wrote in message news:<124b8f0d.0402040443.5710f8f0_at_posting.google.com>...
> > Is there any way for Oracle (using standard SQL rather than PL/SQL) to
>
> by that I assume you mean using SQL*Plus versus some other ORACLE
> client program (such as an ODBC client like ACCESS)?
>
> > suppress the newline character when returning a list of rows.
>
> I do not think you can totally suppress it. In SQL*Plus there is a
> option to set the line size. Look up the options in the SET command.
> There is also an option to define the record separator character,
> IIRC. If so, it too is an option to the SET command.
>
> > I would like to get a single row output back comprising the following:
> >
> > "john, james, jenny, jill, benny"
> >
> > in other words, all names separated by the a comma followed by space.
> >
> > Many thanks in advance for you help.
> >
> > Shuaib
>
> The manual is your friend. (You likely just needed the right words to
> look it up in the index, right?)
>
> HTH,
> ed

Shuaib, what you really asking to do is to convert rows into columns. Check the group archives looking for "pivot tables". There was a couple of posts under the comp.databases.oracle.misc or server board just yesterday on this subject. You can search the group archives via http://groups.google.com if your normal newreader does not provide access.

You can also check the asktom site: http://asktom.oracle.com. It has writeup for performing this task using object types.

HTH -- Mark D Powell -- Received on Fri Feb 06 2004 - 15:34:29 CET