RE: Can I concatenate several rows without a procedure?
yeah,
but it's a convoluted requirement. if they really wanted to retrieve all
rows in one column, why didn't they use a Clob instead???
:)
Tom Mercadante Oracle Certified Professional
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003
5:55 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Can I concatenate several rows without a
procedure?Oh my, that
*is* convoluted. :)
Stephane Faroult
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED]
10/21/2003 02:04 PM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]> cc:
Subject: Re: Can I concatenate several
rows without a procedure?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> > [EMAIL PROTECTED]>
>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_queryVERSEOh,
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 ofthe 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.verse10 and x.lvl =
y.piecemax11* order by x.verseSQL> 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:
[EMAIL PROTECTED]Fat City Network Services --
858-538-5051 http://www.fatcity.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed
from). You mayalso send the HELP command for other information (like
subscribing).
RE: Can I concatenate several rows without a procedure?
My guess is your are correct.
Try this:
select rpad('a',4000,'a') from dual
and
select rpad('a',5000,'a') from dual
both display 4000 chars only.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, October 22, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L
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
> 3from (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
> [EMAIL PROTECTED]
>
> > 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
> > 3from sliced_kipling
> > 4connect 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
> > 10and 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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
[EMAIL PROTECTED]
__
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the na
RE: Can I concatenate several rows without a procedure?
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 > 3from (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 > [EMAIL PROTECTED] > > > 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 > > 3from sliced_kipling > > 4connect 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 > > 10and 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
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 3from (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 [EMAIL PROTECTED] > SQL> select * from sliced_kipling; > > VERSEPIECE 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 > 10and 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Can I concatenate several rows without a procedure?
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 > 3from (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 > [EMAIL PROTECTED] > > > 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 > > 3from sliced_kipling > > 4connect 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 > > 10and 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
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 3from (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 [EMAIL PROTECTED] > SQL> select * from sliced_kipling; > > VERSEPIECE 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 > 10and 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Can I concatenate several rows without a procedure?
Oh my, that *is* convoluted. :) Stephane Faroult <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/21/2003 02:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: Can I concatenate several rows without a procedure? 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 > > [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Can I concatenate several rows without a procedure?
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 > > [EMAIL PROTECTED] > > 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 3from sliced_kipling 4connect 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 10and 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
go to asktom.oracle.com and search for stragg. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
Humm, what purpose would that serve? How would that be useful? My perception is a row of data is independently referenced and if you concat n rows into one string... the data would no longer be referenced individually. No doubt someone on this list can give you a solution, but Im sure it would need to be in a procedure, looping thru and concating as it went... which was not your original criteria. -Original Message- Sent: Tuesday, October 21, 2003 2:44 PM To: Multiple recipients of list ORACLE-L 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 > [EMAIL PROTECTED] > > __ > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Bob Metelsky > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Bob Metelsky INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Can I concatenate several rows without a procedure?
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 > [EMAIL PROTECTED] > > __ > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Bob Metelsky > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
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 [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Bob Metelsky INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
Yes. > -Original Message- > Jake Johnson > > I am trying to concatenate several records with simple sql. > Is this possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Can I concatenate several rows without a procedure?
Jake Can you provide a simple example of what you have in mind? Several possibilities occur to me, but I'm not sure which will suit your need. Concatenating the columns of a row, the same column from several rows, all columns of several rows? Also, when you say "several", how many specifically do you need? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 20, 2003 7:09 PM To: Multiple recipients of list ORACLE-L Hello, I am trying to concatenate several records with simple sql. Is this possible? -- Thanks, Jake Johnson [EMAIL PROTECTED] __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
