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;