在Oracle XE 11.2中与PostgreSQL的array_agg等价的函数是什么？

Question

我有一个Oracle 11g XE数据库，我有一个查询结果集：

ID   Category
1    Cat1
1    Cat2
2    Cat3
2    Cat4

我想获取所有相关类别的唯一id，并在同一行中以逗号分隔的方式呈现，如下所示：

ID   Categories
1    Cat1,Cat2
2    Cat3,Cat4

我之前使用的是Postgres，array_agg函数对我非常有帮助。在Oracle 11g XE中如何获得同样的结果呢？

- Murmelto

---

简而言之：LISTAGG()在11g中添加，可以提供所需的结果集。 - Ben

Answer 1

除非您将其用于存储过程以将输出保存为数组（或集合），否则使用带有LISTAGG的查询应该足够，并且提供相同的输出。

select ID , LISTAGG(Category,',') WITHIN GROUP ( ORDER BY ID ) Categories
FROM yourtable GROUP BY ID;

在Oracle中，我们没有像array_agg这样的直接转换函数。但是，您可以创建用户定义的集合类型，然后使用CAST和COLLECT函数将其转换为NESTED TABLE，以获得相同的期望输出结果。

首先，创建一个集合TYPE。

create or replace TYPE categorytype as TABLE OF VARCHAR2(20);

现在，运行此查询等同于使用string_agg或LISTAGG，尽管categories是一个数组或集合，而不是一个字符串。

select id, CAST ( COLLECT(Category) as categorytype ) categories
FROM yourtable group by id;

| ID | CATEGORIES |
|----|------------|
|  1 |  Cat1,Cat2 |
|  2 |  Cat3,Cat4 |

演示

- Kaushik Nayak

---

非常感谢你，伙计。你的建议对我很有帮助。 - Murmelto

我已经向表中添加了以下两条记录：
INTO yourtable (ID, Category) VALUES (2, 'Cat4') INTO yourtable (ID, Category) VALUES (2, 'Cat4')当我使用listagg查询时，我得到了"2 Cat3,Cat4,Cat4i,Cat4"的结果。如何避免在listagg中出现重复值？ - Murmelto

Answer 2

Oracle有一个函数listagg()，它与Postgres的string_agg()非常接近。两者似乎都可以完成你想要的功能。

然而，array_agg()使用SQL表达式创建数组。我认为Oracle不支持将数组作为SQL语句中的本地类型（尽管数组是PL/SQL的一部分）。因此，没有相应的等效函数。

- Gordon Linoff