get data from row to columns using sql

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'

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.

Code:

---

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 :

Code:

---

select stragg(ename)
    from sample;

---