Oracle PIVOT子句中的用户定义聚合函数

Unable to use user-defined aggregate functions in Oracle PIVOT clause.

无法在Oracle PIVOT子句中使用用户定义的聚合函数。

I created a user-defined aggregate function called string_agg.
I am able to use it in a simple statement such as...

我创建了一个名为string_agg的用户定义聚合函数。我可以在一个简单的声明中使用它，例如......

select id, string_agg(value) from
(
    select 'user1' as id, 'BMW' as value,   'CAR'      as type from dual    union
    select 'user1' as id, 'Audi' as value,  'CAR'      as type from dual    union
    select 'user2' as id, 'Honda' as value, 'CAR'      as type from dual    union
    select 'user1' as id, 'Dell' as value,  'COMPUTER' as type from dual    union
    select 'user1' as id, 'Sony' as value,  'COMPUTER' as type from dual    union
    select 'user2' as id, 'HP' as value,    'COMPUTER' as type from dual 
)
group by id, type

Results in:

ID TYPE STRING_AGG(VALUE)
user1       CAR             Audi,BMW
user1       COMPUTER        Dell,Sony
user2       CAR             Honda
user2       COMPUTER        HP

However when I try to use the same function in a pivot clause

select * from
( 
    select id, type, string_agg(value) as value from
    (
        select 'user1' as id, 'BMW' as value,   'CAR'      as type from dual    union
        select 'user1' as id, 'Audi' as value,  'CAR'      as type from dual    union
        select 'user2' as id, 'Honda' as value, 'CAR'      as type from dual    union
        select 'user1' as id, 'Dell' as value,  'COMPUTER' as type from dual    union
        select 'user1' as id, 'Sony' as value,  'COMPUTER' as type from dual    union
        select 'user2' as id, 'HP' as value,    'COMPUTER' as type from dual 
    )
    group by id, type
)
PIVOT (string_agg(value) FOR id IN ('user1' user1, 'user2' user2) );

I get the following error...

ORA-56902: expect aggregate function inside pivot operation

Expected result is...

TYPE USER1 USER2
COMPUTER    Dell,Sony   HP       
CAR         Audi,BMW    Honda

2 个解决方案

#1

Pivot doesn't have to be over the same aggregate function:

Pivot不必超过相同的聚合函数：

select * from
( 
    select id, type, LISTAGG(value) WITHIN GROUP (ORDER BY 1) as value from
    (
        select 'user1' as id, 'BMW' as value,   'CAR'      as type from dual    union
        select 'user1' as id, 'Audi' as value,  'CAR'      as type from dual    union
        select 'user2' as id, 'Honda' as value, 'CAR'      as type from dual    union
        select 'user1' as id, 'Dell' as value,  'COMPUTER' as type from dual    union
        select 'user1' as id, 'Sony' as value,  'COMPUTER' as type from dual    union
        select 'user2' as id, 'HP' as value,    'COMPUTER' as type from dual 
    )
    group by id, type
)
PIVOT (max(value) FOR id IN ('user1' user1, 'user2' user2) );

#2

What about trying wmsys.wm_concat instead of your user-defined aggregate?

如何尝试使用wmsys.wm_concat而不是用户定义的聚合？

#1