Is there any function in oracle similar to group_concat in mysql? [duplicate]

Question

My inputs are in this way.

col1   col2 1      a 1      b 2      c 2      d 2      e 

O/p: Should Be like

col1    col2 1       a,b 2       c,d,e 

I want a query that can be fired at DB level. I've tried various ways, but wasn't able to make this out...

Answer 1

11g and higher: Use listagg:

SELECT      col1,     LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names" FROM table_x GROUP BY col1 

10g and lower: One method is to use a function:

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val  in  number)   RETURN VARCHAR2 IS   return_text  VARCHAR2(10000) := NULL; BEGIN   FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP     return_text := return_text || ',' || x.col2 ;   END LOOP;   RETURN LTRIM(return_text, ','); END; / 

To use the function:

select col1, get_comma_separated_value(col1) from table_name 

Note: There is an (unsupported) function WM_CONCAT available on certain older versions of Oracle, which might help you out - see here for details.

In MySQL:

SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1