Mmmmmmm ... Hot buttered biscuits ... On Fri, Nov 4, 2011 at 12:11 PM, ddf <orat...@msn.com> wrote:
> > > On Nov 4, 12:47 am, SANDEEP REDDY <tosandeepyan...@gmail.com> wrote: > > I Already Posted The Example On How to diaplay the records > > > > A single column Of all Records To be displayed in a single > > row...i.e.,Multiple rows in a single row.. > > > > I am Using Oracle 10g Ver 2 (10.1.0.2) > > > > Obviously you're using 10.2.0.1 if it is indeed 10gR2. Since you have > not (or won't) posted any code you've written I must presume you have > absolutely no clue where to begin. We'll start by building and > loading a sample table: > > SQL> create table snorp( > 2 col1 number, > 3 col2 varchar2(12), > 4 col3 number > 5 ); > > Table created. > > SQL> > SQL> insert /*+ append */ all > 2 into snorp > 3 (col1, col2, col3) > 4 values(1,'APPLE',1) > 5 into snorp > 6 (col1, col2, col3) > 7 values(2,'BALL',1) > 8 into snorp > 9 (col1, col2, col3) > 10 values(3,'CAT',1) > 11 into snorp > 12 (col1, col2, col3) > 13 values(4,'DOG',1) > 14 into snorp > 15 (col1, col2, col3) > 16 values(5,'ELEPHANT',1) > 17 into snorp > 18 (col1, col2, col3) > 19 values(6,'FOX',1) > 20 into snorp > 21 (col1, col2, col3) > 22 values(7,'GIRAFFE',1) > 23 into snorp > 24 (col1, col2, col3) > 25 values(8,'HYENA',1) > 26 select * from dual; > > 8 rows created. > > SQL> > SQL> commit; > > Commit complete. > > SQL> > > Let's display the contents to verify the necessary data is present: > > SQL> select col3, col2 from snorp order by col2; > > COL3 COL2 > ---------- ------------ > 1 APPLE > 1 BALL > 1 CAT > 1 DOG > 1 ELEPHANT > 1 FOX > 1 GIRAFFE > 1 HYENA > > 8 rows selected. > > SQL> > > We'll start by using an (undocumented in 10g) function named wm_concat > to create a concatenated list of our animals: > > SQL> select col3, wm_concat(col2) list > 2 from (select col3, col2 from snorp order by col2) > 3 group by col3; > > COL3 LIST > ---------- ------------------------------------------------ > 1 APPLE,BALL,CAT,ELEPHANT,GIRAFFE,HYENA,FOX,DOG > > > SQL> > > Hot buttered biscuits from Bimidgi, the data isn't in order! Let's > see if we can get it into order: > > SQL> select list > 2 from > 3 (select col3, translate(wm_concat(col2), ',',' ') list > 4 from (select col3, col2 from snorp order by col2) > 5 group by col3); > > LIST > ------------------------------------------------ > APPLE BALL CAT ELEPHANT GIRAFFE HYENA FOX DOG > > SQL> > > Well, that didn't work; let's go with PL/SQL (after all, this IS the > PL/SQL group): > > SQL> declare > 2 otptstg varchar2(32767):=null; > 3 > 4 cursor get_data is > 5 select col2 > 6 from snorp > 7 order by col2; > 8 > 9 begin > 10 for rec in get_data loop > 11 otptstg:=otptstg||rec.col2||' '; > 12 end loop; > 13 > 14 dbms_output.put_line(otptstg); > 15 > 16 end; > 17 / > APPLE BALL CAT DOG ELEPHANT FOX GIRAFFE HYENA > > PL/SQL procedure successfully completed. > > SQL> > > That worked. Is there a non-PL/SQL approach? Yes, it's a function > named stragg (Tom Kyte wrote it some time back). We'll also use > translate() to get rid of the pesky commas: > > SQL> select translate(catlist,',',' ') cat_list from > 2 (select col3, stragg(col2) catlist > 3 from snorp > 4 group by col3); > > CAT_LIST > ------------------------------------------------- > APPLE BALL CAT DOG ELEPHANT FOX GIRAFFE HYENA > > SQL> > > Of course the code for stragg can be modified to not use a comma and > if we rebuild it that way we can do: > > SQL> select catlist from > 2 (select col3, stragg(col2) catlist > 3 from snorp > 4 group by col3); > > CATLIST > -------------------------------------------------- > APPLE BALL CAT DOG ELEPHANT FOX GIRAFFE HYENA > > SQL> > > which produces the desired output. > > Next time try to solve the problem yourself and come to us for help. > > > David Fitzjarrell > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
