OraFAQ Forum: SQL & PL/SQL » Obtaining values of multiple Records in a single row

Home » SQL & PL/SQL » SQL & PL/SQL » Obtaining values of multiple Records in a single row

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Obtaining values of multiple Records in a single row [message #1325]

Sun, 21 April 2002 20:28

O. Vamsi Krishna
Messages: 3
Registered: January 2002

Junior Member

Hi,

I have an issue with SQL.
If i have the following records in a column "A" of a table "X"
John
Jack
Smith
Jones
Blake
Clark

Is it possible to obtain the result of a SQL as

John, Jack, Smith, Jones, Blake, Clark

The records should be obtained as a single string seperated by commas.
This is to be obtained in a single SQL. I can easily obtain this through a procedure but i want it in a single SQL (with no user defined function calls)

Thanks and Regards
Vamsi

Report message to a moderator

Re: Obtaining values of multiple Records in a single row [message #1334 is a reply to message #1325]

Mon, 22 April 2002 08:59

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> get f1
1 create or replace function f1 (n varchar2) return varchar2 is
2 retval varchar2(200);
3 cursor c1 is select ename from emp;
4 begin
5 for crec in c1 loop
6 if c1%rowcount=1 then
7 retval:= crec.ename;
8 else
9 retval:=retval||','||crec.ename;
10 end if;
11 end loop;
12 return retval;
13* end;
SQL> /

Function created.

SQL> ed
Wrote file afiedt.buf

1* select f1(ename) from emp
SQL> /

F1(ENAME)
----------------------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

F1(ENAME)
----------------------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

14 rows selected.

SQL>

Report message to a moderator

Previous Topic:	pl/sql - equation
Next Topic:	DML usage in a sp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 17:31:47 CDT 2024