On Nov 4, 12:37 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > 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- Hide quoted text - > > - Show quoted text -
Yet another twist on the stragg solution: SQL> column col3 noprint SQL> SQL> select col3, stragg(col2) catlist 2 from snorp 3 group by col3; CATLIST --------------------------------------------- APPLE BALL CAT DOG ELEPHANT FOX GIRAFFE HYENA SQL> Setting col3 to not print reduces the solution to a single query versus an in-line view. Oh, I prefer my biscuits with gravy. 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
