RE: Can I concatenate several rows without a procedure?

From: Larry Elkins <elkinsl_at_flash.net>
Date: Tue, 21 Oct 2003 18:44:25 -0800
Message-ID: <F001.005D3E75.20031021184425@fatcity.com>

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

Received on Tue Oct 21 2003 - 21:44:25 CDT