Merging Query Result into single row - Oracle

Question

Can I do like this in oracle,.? I have some data like this:

No | Data |
===========
 1 | A    |
 1 | B    |
 1 | C    |
 1 | D    |

Is there any query that can produce a result like this,.?

No | Data       |
=================
 1 | A, B, C, D |

Many thanks :D

possible duplicate of [How can I combine multiple rows into a comma-delimited list in Oracle?](http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle) — APC, May 09 '12 at 13:20
This question has been asked very many times on SO. The thread I link to above contains all the common answers. you will find at least one which will help you whichever version of Oracle you're using. — APC, May 09 '12 at 13:25
This technique is called `string aggregation`. See the questions tagges as such: http://stackoverflow.com/questions/tagged/string-aggregation. — Rob van Wijk, May 10 '12 at 08:12

score 1 · Answer 1 · answered May 09 '12 at 09:21

1

Maybe this page shows what you are looking for.

answered May 09 '12 at 09:21

sebastian

2,427
4
32
37

score 1 · Answer 2 · answered Jun 18 '15 at 17:37

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( ID, DATA ) AS
          SELECT 1, 'A' FROM DUAL
UNION ALL SELECT 1, 'B' FROM DUAL
UNION ALL SELECT 1, 'C' FROM DUAL
UNION ALL SELECT 1, 'D' FROM DUAL
UNION ALL SELECT 2, 'E' FROM DUAL
UNION ALL SELECT 2, 'F' FROM DUAL;

Query 1:

SELECT ID,
       LISTAGG( DATA, ',' ) WITHIN GROUP ( ORDER BY DATA ) AS AGGREGATED_DATA
FROM   TEST
GROUP BY ID

Results:

| ID | AGGREGATED_DATA |
|----|-----------------|
|  1 |         A,B,C,D |
|  2 |             E,F |

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

In Oracle we can use wm_concat function. Here is the query for example above:

SELECT no, wm_concat(data) from table group by no

reference: wm_concat

edited May 23 '17 at 12:22

Community

1
1

answered May 09 '12 at 09:43

Praditha

1,162
5
24
45

1

Note that wm_concat is undocumented, so use at your own risk. – Rob van Wijk May 09 '12 at 10:44
Very nice:with my_table as ( select 1 num, 'A' col from dual union select 1 num, 'B' col from dual union select 1 num, 'C' col from dual union select 1 num, 'D' col from dual union select 2 num, 'E' col from dual union select 2 num, 'F' col from dual union select 2 num, 'G' col from dual union select 2 num, 'H' col from dual ) select num, wm_concat(col) from my_table group by num ; – Roger Cornejo May 09 '12 at 11:57

score 0 · Answer 4 · edited Jun 18 '15 at 19:22

0

select 
   no,
   rtrim (xmlagg (xmlelement (d, data|| ',')).extract ('//text()'), ',') data
from 
   table_name
group by 
   no
;

edited Jun 18 '15 at 19:22

josliber

43,891
12
98
133

answered Jun 18 '15 at 17:30

SVaidya

167
2
10

Merging Query Result into single row - Oracle

4 Answers4