query to concatenate three record in one row

[binoyshil]

i want to concatenate values of three column in one row

for example my data is:
Binoy> select * from fpfm_nominies where emp_no='1283L';

EMP_NO NOMINEE
------- -------------------------
1283L Lal Mathi
1283L POONAM KUMARI
1283L SUMAN KUMARI .
it generates three different records with same emp_no but different
nominee. now i want to show names of all nominee in one row through select query only.something like this:

emp_no name
-------- -------------------------------------------
1283L Lal Mathi,POONAM KUMARI,SUMAN KUMARI


can anyone help me out.

[LKBrwn_DBA]

Similar questions have been asked time and again, why dont you search first?

Read this

[thomasp]

This is taken from AskTom.

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 if you run the query as
select emp_no, stragg(nominee)
from fpfm_nominies
group by emp_no;

you should get your desired result.