get data from row to columns using sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: get data from row to columns using sql

  1. #1
    Join Date
    Jun 2005
    Posts
    20

    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'

  2. #2
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    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;
    Last edited by thomasp; 04-07-2006 at 11:45 AM.
    thomasp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width