Oracle 11g r2 string aggregation

Question

I have a table with ID and location columns and I'm trying to combine multiple rows into comma separated listed. For eg:

Source table:   ID    Location      Name
                 1        USA       Bob 
                 1        Brazil    Bob
                 1        Russia    Bob
                 2        India     Emily
                 2        China     Emily

My target table should get the values like this

Target table:   ID    Location             Name
                 1     USA, Brail, Russia  Bob
                 2     India, China        Emily

How can I Perform this using oracle 11g R2?

Gordon Linoff · Answer 1 · 2013-04-04T17:42:10.893

1

You can use listagg():

select id, listagg(location, ', ') within group (order by id) as location
from source
group by id

This does not guarantee the original ordering in the table. SQL tables are inherently unordered, so to guarantee this, you would need a distinct row id for each row. This following might get close:

select id, listagg(location, ', ') within group (order by seqnum) as location
from (select *, rownum as seqnum from source) s
group by id

edited Apr 04 '13 at 17:42

answered Apr 04 '13 at 17:32

Gordon Linoff

1,242,037
58
646
786

Missing within keyword – user1751356 Apr 04 '13 at 17:36
I tried this select id, wm_concat(Location) as locations from sourcetable group by id and this seems to be working. – user1751356 Apr 04 '13 at 17:49
But my solutions is not working if I need to select extra columns from source. – user1751356 Apr 04 '13 at 17:58

Oracle 11g r2 string aggregation

1 Answers1