Is there any way in ORACLE to combine multiple rows of rows into one using two tables where the final values ​​are separated by commas?

Question

Is there a way to combine multiple rows of rows of 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
BMW

B SEAT
B FIAT
C FORD

Is there an easy way to "concat" to a comma-separated string?

Answer 1

Take a look at LISTAGG

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

Sort of:

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

. , , .

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