RE: Can I concatenate several rows without a procedure?

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 22 Oct 2003 04:49:25 -0800
Message-ID: <F001.005D3F7B.20031022044925@fatcity.com>

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-----
Sent: Tuesday, October 21, 2003 5:55 PM
To: Multiple recipients of list ORACLE-L

Oh my, that *is* convoluted. :)

        Stephane Faroult <sfaroult_at_oriole.com> Sent by: ml-errors_at_fatcity.com

 10/21/2003 02:04 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com> 
        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
> > jake_at_plutoid.com
> >

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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: ListGuru_at_fatcity.com (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).

Received on Wed Oct 22 2003 - 07:49:25 CDT