等效于Oracle XE 11.2中PostgreSQL的array_agg [英] Equivalent of PostgreSQL's array_agg in Oracle XE 11.2

问题描述

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

I have a Oracle 11g XE database and I have a query the result set:

ID   Category
1    Cat1
1    Cat2
2    Cat3
2    Cat4

我要以便以逗号分隔同一行中所有相关类别的唯一ID，如下所示：

I want to get distinct id's with all related categories in same row as comma separated like this

ID   Categories
1    Cat1,Cat2
2    Cat3,Cat4

我以前使用过Postgres，而 array_agg 帮助了我。如何在Oracle 11g XE中获得相同的结果？

I was using Postgres before and array_agg helped me there. How can I get same result in Oracle 11g XE?

推荐答案

除非您在存储过程中使用它来将输出另存为数组（或集合），使用 LISTAGG 的查询就足够了，并提供相同的输出。

Unless you are using it in a stored procedure to save the output as an array(or collection), a query with LISTAGG should be sufficient and gives the same output.

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

在oracle中，我们没有像 array_agg 。但是，您可以创建用户定义的集合类型，然后使用 CAST 和 COLLECT 函数将其转换为嵌套表以获得相同的期望输出。

In oracle, we do not have have a straightforward conversion function like array_agg. However, you can create a user-defined collection type, then use CAST and COLLECT functions to convert it to a NESTED TABLE to get the same desired output.

首先，创建一个集合 TYPE 。

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

现在，运行此查询等效于使用 string_agg 或 LISTAGG ，尽管类别是数组或 collection ，而不是字符串

Now, running this query is equivalent to using string_agg or LISTAGG, although categories is an array or collection, rather than a string.

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

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

演示

这篇关于等效于Oracle XE 11.2中PostgreSQL的array_agg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！