5

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
nick
  • 51
  • 1
  • 3
  • "select id, string_agg(value) from table1" will give an ORA-00937 error since you don't have a group by clause. Can you give us a complete example of some sql with the pivot and your aggregate function in that fails. – Gerrat Aug 03 '11 at 20:57
  • Thanks for the feedback. Edited original post to provide a detailed example – nick Aug 03 '11 at 22:04
  • It looks like you're using the string_agg function from here: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php. I can't find any reason for this to fail. Maybe a bug (maybe pivot doesn't work with user defined aggregate functions)? If you change your 2nd occurrence of string_agg to `max`, it gives the results you want though – Gerrat Aug 04 '11 at 03:41
  • True. I thought of this as well. Just wanted to find out why I cant use a user-defined agg function in PIVOT. – nick Aug 04 '11 at 13:42
  • I did not find any reference to a restriction in the official SQL reference for Oracle 11gR2, but I run into the same problem. – Benoit Feb 28 '12 at 14:13

2 Answers2

0

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) );
Tegiri Nenashi
  • 3,066
  • 2
  • 19
  • 20
  • This doesn't produce the same output as his custom string_agg function (but if you change the line containing LISTAGG back to what OP had, it still works...and produces the desired output). – Gerrat Aug 04 '11 at 03:06
  • True. I thought of this as well. Just wanted to find out why I cant use a user-defined agg function in PIVOT. – nick Aug 04 '11 at 13:42
0

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

Dave Routledge
  • 1
  • I should have clarified... the demo is very simple - what I am after is a function where i can pass in a number or a string and it determines if it should calculate the sum or concat – nick Aug 04 '11 at 13:52