On Nov 4, 12:47 am, SANDEEP REDDY <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
