User-Defined Aggregate functions in Oracle PIVOT clause
Unable to use user-defined aggregate functions in Oracle PIVOT clause.
I created a user-defined aggregate function called string_agg.
I am able to use it in a simple statement such as...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
Pivot doesn't have to be over the same aggregate function:
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) );
What about trying wmsys.wm_concat instead of your user-defined aggregate?
精彩评论