yeah,
but it's a convoluted requirement. if they really wanted to retrieve all
rows in one column, why didn't they use a Clob instead???
:)
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 5:55 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Can I concatenate several rows without a procedure?
Oh my, that *is* convoluted. :)
Stephane Faroult <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]10/21/2003 02:04 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Can I concatenate several rows without a procedure?
Jake Johnson wrote:
>
> The following query returns 33 records.
>
> SYS0 freestyle!! 12-MAY-02
> SYSTEM5 freestyle!! 12-MAY-02
> OUTLN11 freestyle!! 12-MAY-02
> ....
>
> But, I would like to have all 33 records appended together to have one long record.
>
> SYS0 freestyle!! 12-MAY-02SYSTEM5 freestyle!! 12-MAY-02OUTLN11 freestyle!! 12-MAY-02....
>
> Thanks again,
> Jake
>
> On Tue, Oct 21, 2003 at 07:49:25AM -0800, Bob Metelsky wrote:
> > select username||user_id||' freestyle!! '|| created as concat from
> > all_users;
> >
> >
> > Hello,
> > I am trying to concatenate several records with simple sql. Is this
> > possible?
> >
> >
> > --
> > Thanks,
> > Jake Johnson
> > [EMAIL PROTECTED]
> >
SQL> select * from sliced_kipling;
VERSE PIECE CHUNK
---------- ---------- --------------------------------------------------
1 1 Oh, East is East,
1 2 and West is West,
1 3 and never the twain shall meet,
2 1 Till Earth and Sky stand
2 2 presently at God's great Judgment Seat;
3 1 But there is neither East nor West,
3 2 Border,
3 3 nor Breed,
3 4 nor Birth,
4 1 When two strong men stand face to face,
4 2 tho' they come from the ends of the earth!
11 rows selected.
SQL> @magic_query
VERSE
--------------------------------------------------------------------------------
Oh, East is East, and West is West, and never the twain shall meet,
Till Earth and Sky stand presently at God's great Judgment Seat;
But there is neither East nor West, Border, nor Breed, nor Birth,
When two strong men stand face to face, tho' they come from the ends of
the earth!
SQL> l
1 select translate(ltrim(x.text, '/'), '/', ' ') verse
2 from (select verse, level lvl, sys_connect_by_path(chunk, '/') text
3 from sliced_kipling
4 connect by verse = prior verse
5 and piece - 1 = prior piece) x,
6 (select verse, max(piece) piecemax
7 from sliced_kipling
8 group by verse) y
9 where x.verse = y.verse
10 and x.lvl = y.piecemax
11* order by x.verse
SQL>
I am not sure though that I satisfy the 'simple SQL' requirement :-).
Stephane Faroult
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
