Oracle 10g中的LISTAGG替代 [英] LISTAGG alternative in Oracle 10g

问题描述

我是Oracle的新手. 卡在下面: 我有以下2张桌子:

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

站点:

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

人:

**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

• 几点:

-该解决方案在Oracle 10g和11g中也都适用.

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

-为了简洁起见，我在上面的示例中给出了一小部分数据，但是在我的产品场景中，一个Person可以与1000+个位置相关联，并且可以有1000+个这样的人，因此解决方案不应该因此而中断情况！

-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!

任何帮助将不胜感激.

谢谢.

推荐答案

尝试使用XMLAGG这样:

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;

如果您需要以特定顺序进行串联，例如按SiteId的升序，然后在xmlagg中添加order by子句:

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;

如果要显示分配给站点100的所有人员的结果:

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;

这篇关于Oracle 10g中的LISTAGG替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！