如何通过独特的过滤器使用 Oracle 的 LISTAGG 函数?

2021-12-27 00:00:00 group-by oracle11g oracle aggregate-functions

我有一张这样的桌子:

group_id  name  
--------  ----
1         David
1         John
1         Alan
1         David
2         Julie
2         Charles

我想要以下结果:

group_id  names
--------  -----
1         'Alan, David, John'
2         'Charles, Julie'

我可以使用以下查询:

select group_id, 
       listagg(name, ',') within group (order by name) as names
from demotable
group by group_id

要得到这个(非常相似的结果):

To get this (very similar result):

group_id  names
--------  -----
1         'Alan, David, David, John'
2         'Charles, Julie'

有什么想法可以通过 LISTAGG 调用中的唯一性过滤名称吗?

Any ideas how I can filter the names by uniqueness in the LISTAGG call?

推荐答案

我今天没有可用的 11g 实例，但您不能使用:

I don't have an 11g instance available today but could you not use:

SELECT group_id,
       LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names
  FROM (
       SELECT UNIQUE
              group_id,
              name
         FROM demotable
       )
 GROUP BY group_id

相关文章