oracle非常量不能用于privot_database – Oracle PIVOT子句中的用户定义聚合函数

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

我创建了一个名为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

结果是：

ID TYPE STRING_AGG(VALUE)

user1 CAR Audi,BMW

user1 COMPUTER Dell,Sony

user2 CAR Honda

user2 COMPUTER HP

但是当我尝试在pivot子句中使用相同的函数时

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) );

我收到以下错误…

ORA-56902: expect aggregate function inside pivot operation

预期结果是……

TYPE USER1 USER2

COMPUTER Dell,Sony HP

CAR Audi,BMW Honda