How to retrieve two columns data in A,B format in Oracle

Question

I have two columns in oracle database

+---------+---------+
| Column1 | Column2 |
+---------+---------+
| A       | 1       |
| A       | 2       |
+---------+---------+

I want to retireive the data like i will get data as result

+---------+---------+
| Column1 | Column2 |
+---------+---------+  
| A       | 1,2     |
+---------+---------+

Please provide me the solution.

Answer 1

Tim Hall has a pretty canonical list of string aggregation techniques in Oracle.

Which technique you use depends on a number of factors including the version of Oracle and whether you are looking for a purely SQL solution. If you are using Oracle 11.2, I'd probably suggest using LISTAGG

SELECT column1, listagg( column2, ',' ) WITHIN GROUP( order by column2 )
  FROM table_name
 GROUP BY column1

If you are using an earlier version of Oracle, assuming you don't need a purely SQL solution, I would generally prefer using the user-defined aggregate function approach.

Answer 2

If you have got 10g, then you have to go through the function below:

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;
/

So, you can do like:

select col1, get_comma_separated_value(col1) from table_name

Fiddle here

If you have got oracle 11g, you can use listagg :

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

Fiddle here for Listagg

For mysql, its gonna be simple:

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

Answer 3

All abow answers are correct and I want to add one case to solve small problem. In my case my_column1 type was nvarchar2 but text was number and the bellow code does not work and display me only whitespace:

select group_id, listagg( t.my_column1 || '-' || to_char(t.doc_date,'dd.mm.yyyy') ||  ' ') within group(order by doc_date) 
 from my_table t
 group by group_id

when I wrote like this it works.

select group_id, listagg( to_char(t.my_column1) || '-' || to_char(t.doc_date,'dd.mm.yyyy') ||  ' ') within group(order by doc_date) 
 from my_table t
 group by group_id

I hope my feedback would save someone's time

Answer 4

On my oracle version 10 it do the job:

SELECT column1, wm_concat( column2)
  FROM table_name
 GROUP BY column1