| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can I concatenate several rows without a procedure?
That is post fix binary tree POST FIX, right?
-----Original Message-----
Sent: 22 October 2003 10:44
To: Multiple recipients of list ORACLE-L
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
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: SinardyXing_at_bkgcomsvc.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 - 05:04:26 CDT
 |
 |