RE: Can I concatenate several rows without a procedure?

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 22 Oct 2003 06:04:26 -0800
Message-ID: <F001.005D3F81.20031022060426@fatcity.com>

My guess the output will be limited to 4000 characters.

Waleed

-----Original Message-----
Sent: Wednesday, October 22, 2003 12:24 AM To: Multiple recipients of list ORACLE-L

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

-- 
Thanks,
Jake Johnson
jake_at_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_at_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_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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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 Wed Oct 22 2003 - 09:04:26 CDT