结果:
但是,当我尝试在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
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) );
试试wmsys怎么样?Wm_concat而不是用户定义的聚合?