get data from row to columns using sql

[balasmg]

Hi all,

Is there any way to display vertical data horizontally though SQL(without using PL/SQL) for e.g if there is a table emp with columsn (empid,empname).The data is as follows:

select eid, ename from sample

EID ENAME
---------- --------------------
1 Ram
2 Rajesh
3 Ramana
4 Mukesh
5 Ramesh
6 Karnam
7 Sibjit
8 Rahul
9 Mohan
10 Ganesh
..
1000 Raju
to display all the name in one row.
Ram Rajesh Ramana Mukesh Ramesh Karnam Sibjit Rahul Mohan Ganesh

I have worked by refering asktom article by creating an object but still not got as my above expected result

CREATE TABLE sample(eid NUMBER, ename VARCHAR2(20));

INSERT INTO sample(eid, ename) VALUES(1,'Ram');
INSERT INTO sample(eid, ename) VALUES(2,'Rajesh');
INSERT INTO sample(eid, ename) VALUES(3, 'Ramana');
INSERT INTO sample(eid, ename) VALUES(4, 'Mukesh');
INSERT INTO sample(eid, ename) VALUES(5, 'Ramesh');
INSERT INTO sample(eid, ename) VALUES(6, 'Karnam');
INSERT INTO sample(eid, ename) VALUES(7, 'Sibjit');
INSERT INTO sample(eid, ename) VALUES(8, 'Rahul');
INSERT INTO sample(eid, ename) VALUES(9, 'Mohan');
INSERT INTO sample(eid, ename) VALUES(10, 'Ganesh');

COMMIT;

ALTER TABLE sample add(comm number);

UPDATE sample SET comm = 1;

COMMIT;


create or replace type myScalarType as object
( name varchar2(20))

create or replace type myArrayType as table of myScalarType

select
cast ( multiset( select ename
from sample I
WHERE I.comm = O.comm) as myArrayType ) x
FROM sample O
GROUP BY comm

X(NAME)
--------------------------------------------------------------------------------
MYARRAYTYPE(MYSCALARTYPE('Ram'), MYSCALARTYPE('Rajesh'), MYSCALARTYPE('Ramana'),
MYSCALARTYPE('Mukesh'), MYSCALARTYPE('Ramesh'), MYSCALARTYPE('Karnam'), MYSCALA
RTYPE('Sibjit'), MYSCALARTYPE('Rahul'), MYSCALARTYPE('Mohan'), MYSCALARTYPE('Gan
esh'))

i am able to get the result.
i need to know is there any query to get result as

'Ram' 'Rajesh'..........'Ganesh'

[thomasp]

I previously gave a completely wrong answer, that's why I took it out. Ok, this example is from asktom. IF you do a search for stragg, you will see more examples.

create or replace type string_agg_type as object
(
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


And then you can query :
select stragg(ename)
from sample;