database – 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,'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,'CAR'      as type from dual    union
        select 'user1' as id,'CAR'      as type from dual    union
        select 'user2' as id,'COMPUTER' as type from dual    union
        select 'user1' as id,'COMPUTER' as type from dual    union
        select 'user2' as id,'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

解决方法

select * from
( 
    select id,LISTAGG(value) WITHIN GROUP (ORDER BY 1) as value from
    (
        select 'user1' as id,type
)
PIVOT (max(value) FOR id IN ('user1' user1,'user2' user2) );

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!