Message-ID: <F001.005D3E7B.20031021202427@fatcity.com>
Date: Tue, 21 Oct 2003 20:24:27 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Jake Johnson <jake@plutoid.com>
Subject: Re: Can I concatenate several rows without a  procedure?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Very impressive!  I will definitly try this.

Thanks Again,
Jake

On Tue, Oct 21, 2003 at 06:44:25PM -0800, Larry Elkins wrote:
> Stephane,
> 
> Pretty slick trick!!! But I can't believe that you, of all people, didn't
> throw in an analytic just to confuse things even more, plus, avoid that
> second pass on sliced_kipling ;-)
> 
> SQL> l
>   1  select translate(ltrim(text, '/'), '/', ' ') verse
>   2  from (select text, row_number() over (partition by verse order by
> verse, lvl desc) rn
>   3        from (select verse, level lvl, sys_connect_by_path(chunk, '/')
> text
>   4              from sliced_kipling
>   5              connect by verse = prior verse
>   6                 and piece - 1 = prior piece))
>   7* where rn = 1
> SQL> /
> 
> 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!
> 
> And hey, it reduced sorts and consistent gets in this particular case ;-)
> Ok, my head hurts from dumb SQL tricks, someone else take it further from
> here ;-)
> 
> Later,
> 
> Larry G. Elkins
> elkinsl@flash.net
> 
> > 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 :-).
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Larry Elkins
>   INET: elkinsl@flash.net
> 
> 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@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).

-- 
Thanks,
Jake Johnson
jake@plutoid.com

______________________________________________________________________
Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on
Rims, Tires, and Wheel Packages.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jake Johnson
  INET: jake@plutoid.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@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).