Is there a way, in ORACLE, to join multiple row lines into a single one, using two tables, where the final values are separated by commas?

Question

Is there a way to join multiple row lines a single one using two tables where the values are separated by commas in ORACLE?

Example:

Table1

IdN Name 
---------
1   A 
2   B 
3   C 

Table 2

IdC Car
------------
1    Ferrari
1    BMW
2    SEAT
2    FIAT
3    FORD

Result as:

A    Ferrari,BMW
B    SEAT,FIAT
C    FORD

I was wondering if there is something like this:

SELECT NAME,CAR
FROM TABLE1, TABLE2
where TABLE1.IdN=TABLE2.IdC

This returns something like:

A FERRARI
A BMW
B SEAT
B FIAT
C FORD

Is there a simple way to "concat" into a row with comma-separated-values ?

Answer 1

Take a look at LISTAGG

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Something like:

SELECT NAME, LISTAGG(CAR, ',') WITHIN GROUP (ORDER BY CAR) AS CARS
FROM   (SELECT NAME,CAR
FROM TABLE1, TABLE2
where TABLE1.IdN=TABLE2.IdC)
GROUP BY NAME;

Answer 2

look up user defined aggregate functions. if you really need to list them all in one column, you can set up an aggregate function, and it will do that for you.

Declare 
   sql_txt      Varchar2(4000); 
   Rec_cnt      Number; 
Begin 
   Select Count(*) 
     Into Rec_Cnt 
     From User_Types 
    Where Type_Name = 'VCARRAY' 
      And Typecode = 'COLLECTION'; 

  If Rec_Cnt = 0 Then 
     EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE vcArray as table of varchar2(32000)'; 
  END IF; 
END;   
/ 

CREATE OR REPLACE TYPE comma_list_agr_type as object 
  ( 
     data  vcArray, 

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

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

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

     member function 
          ODCIAggregateMerge(self IN OUT comma_list_agr_type, 
                             ctx2 IN comma_list_agr_type) 
          return number 
  ); 
/ 


CREATE OR REPLACE TYPE BODY comma_list_agr_type 
  is 

  static function ODCIAggregateInitialize(sctx IN OUT comma_list_agr_type) 
  return number 
  is 
  begin 
      sctx := comma_list_agr_type( vcArray() ); 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateIterate(self IN OUT comma_list_agr_type, 
                                       value IN varchar2 ) 
  return number 
  is 
  begin 
      data.extend; 
      data(data.count) := value; 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateTerminate(self IN comma_list_agr_type, 
                                         returnValue OUT varchar2, 
                                         flags IN number) 
  return number 
  is 
      l_data varchar2(32000); 
  begin 
      for x in ( select column_value from TABLE(data) order by 1 ) 
      loop 
              l_data := l_data || ',' || x.column_value; 
      end loop; 
      returnValue := ltrim(l_data,','); 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateMerge(self IN OUT comma_list_agr_type, 
                                     ctx2 IN comma_list_agr_type) 
  return number 
  is 
  begin -- not really tested ;) 
      for i in 1 .. ctx2.data.count 
      loop 
              data.extend; 
              data(data.count) := ctx2.data(i); 
      end loop; 
      return ODCIConst.Success; 
  end; 
  end; 
/ 

CREATE OR REPLACE FUNCTION comma_list(input varchar2 ) 
  RETURN varchar2 
  PARALLEL_ENABLE AGGREGATE USING comma_list_agr_type; 
/ 

GRANT EXECUTE ON COMMA_LIST to someuser