oracle distinct with listagg

Question

我有一张桌子。我可以在同一行中使用'，‘来显示表中某列的所有数据。但我不能明确地应用它。请给我hepl

Answer 1

这很棘手。一个简单的建议是使用select distinct

select listagg(col, ',') within group (order by col)
from (select distinct col from t) x;

然而，这使得很难计算其他聚合(或者生成比listagg()结果更多的聚合)。另一种方法是将窗口函数与listagg()结合使用

select listagg(case when seqnum = 1 then col end, ',') within group (order by col)
from (select t.*,
             row_number() over (partition by col order by col) as seqnum
      from t
     ) t