| multiple record query [message #38923] |
Tue, 28 May 2002 03:31  |
Anke
Messages: 8
Registered: November 1999
|
Junior Member |
|
|
how can I display multiple rows of a column as single value for that column.
for example I am having Such Record from Nutzung Table
Flurstueck Nutzung Karte
------- ----------- -----------
20 Wald 1020
20 Wiese 1020
20 Feld 1020
30 Wohnen 1030
30 Parkplatz 1030
10 Wohnen 1010
I want output as :
Flurstueck Nutzung Karte
------- ---------------------------------
20 Wald, Wiese, Feld 1020
30 Wohnen, Parkplatz 1030
10 Wohnen 1010
Thanks,
Anke
|
|
|
|
| Re: multiple record query [message #38924 is a reply to message #38923] |
Tue, 28 May 2002 05:16  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Hi.
all you need to do is to create 2 functions.
***********************************************
SQL> select * from sample;
FLURSTUECK NUTZUNG KARTE
---------- ------------------------------ ----------
20 wald 1020
20 wiese 1020
20 feld 1020
30 wohnen 1030
30 parkplatz 1030
10 wohnen 1010
6 rows selected.
SQL> get f11
1 create or replace function f11(n number) return varchar2 is
2 retval varchar2(200);
3 cursor c1 is select distinct karte from sample where FLURSTUECK=n;
4 begin
5 for crec in c1 loop
6 if c1%rowcount=1 then
7 retval:= crec.karte;
8 else
9 retval:=retval||','||crec.karte;
10 end if;
11 end loop;
12 return retval;
13* end;
SQL> /
Function created.
SQL> get f10
1 create or replace function f10(n number) return varchar2 is
2 retval varchar2(200);
3 cursor c1 is select Nutzung from sample where FLURSTUECK=n;
4 begin
5 for crec in c1 loop
6 if c1%rowcount=1 then
7 retval:= crec.Nutzung;
8 else
9 retval:=retval||','||crec.Nutzung;
10 end if;
11 end loop;
12 return retval;
13* end;
SQL> /
Function created.
SQL> column nutzung format a20
SQL> column karte format a5
SQL> ed
Wrote file afiedt.buf
1* select flurstueck, f10(flurstueck) nutzung, f11(flurstueck) karte from sample group by flurstueck
SQL> /
FLURSTUECK NUTZUNG KARTE
---------- -------------------- -----
10 wohnen 1010
20 wald,wiese,feld 1020
30 wohnen,parkplatz 1030
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
