Re: Can I concatenate several rows without a procedure?

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Tue, 21 Oct 2003 13:04:26 -0800
Message-ID: <F001.005D3E41.20031021130426@fatcity.com>

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).

Received on Tue Oct 21 2003 - 16:04:26 CDT