LISTAGG alternative in Oracle 10g

Question

I am kind of newbie in Oracle. Got stuck in the below: I have the below 2 tables:

Site:

**SiteID|SiteName** 
1      Sydney
2      Newyork
3      Delhi

People:

**RecordID|PeopleID|SiteID**
1         1        1
2         1        2
3         2        2
4         3        1
5         3        2
6         3        3

Now in my query I want an output something like this:

**PeopleID | AssignedSites**
1          Sydney,NewYork
2          Newyork
3          Sydney,NewYork,Delhi

• Few more points:

-The solution should work in Oracle 10g as well as 11g also.

-I have given small subset of data in the above example for brevity.But, in my prod scenario, one Person can be associated with 1000+ locations and there could 1000+ such person, so the solution should not break in that case!

Any help will be highly appreciated.

Thanks in advance.

Answer 1

Try using XMLAGG like this:

select
    p.PeopleID,
    rtrim(xmlagg(xmlelement(e, s.SiteName, ',')).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
group by p.PeopleID;

If you need the concatenation in a particular order, say increasing order of SiteId, then add an order by clause in the xmlagg:

select
    p.PeopleID,
    rtrim(xmlagg(xmlelement(e, s.SiteName, ',')
                   order by s.SiteId).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
group by p.PeopleID;

EDIT:

If you want display result for all those people which are assigned to site 100:

select p.PeopleID,
    rtrim(xmlagg(
                xmlelement(e, s.SiteName, ',') order by s.SiteId
            ).extract('//text()').getclobval(), ',')
from people p
join site s on p.SiteID = s.SiteID
join (
    select distinct PeopleID
    from people
    where siteID = 1
    ) p2 on p.PeopleID = p2.PeopleID
group by p.PeopleID;

Answer 2

listagg() is the obvious choice, but it is not available in Oracle 10. However, even in Oracle 11, listagg() is limited to strings of length 4,000, and you explicitly say "Person can be associated with 1000+ locations".

There are ways around this, using CLOBs, XML, and no doubt other solutions as well. However, what use is a list of locations thousands and thousands of characters long? With so many locations, you are not going to be able to put the result in a standard varchar2() field.

Perhaps summarizing them in the database this way is not the best solution to your actual problem.