Is there a way to round an Oracle crosstab PIVOT?

Question

I want to pivot data using the AVG() function, but I want to round the results to prevent repeating decimals from displaying.

When I try something like this: PIVOT( ROUND( AVG(column_name), 2) FOR ...)

I get an error: ORA-56902: expect aggregate function inside pivot operation

Here is a very simple example of "number of students registered in a course":

CREATE TABLE TBL_EXAMPLE
(
  enrolled NUMBER,
  course   VARCHAR2(50 CHAR)
);

INSERT INTO TBL_EXAMPLE (enrolled, course) VALUES (1, 'math');
INSERT INTO TBL_EXAMPLE (enrolled, course) VALUES (2, 'math');
INSERT INTO TBL_EXAMPLE (enrolled, course) VALUES (2, 'math');
INSERT INTO TBL_EXAMPLE (enrolled, course) VALUES (1, 'english');
INSERT INTO TBL_EXAMPLE (enrolled, course) VALUES (4, 'english');

SELECT * 
  FROM TBL_EXAMPLE 
 PIVOT ( AVG(enrolled) FOR course IN ('math', 'english') );

'math'          'english'
---------------|-------------
1.6666666666...| 2.5

What I want is:

SELECT * 
  FROM TBL_EXAMPLE 
 PIVOT ( ROUND(AVG(enrolled), 2) FOR course IN ('math', 'english') );

'math'          'english'
---------------|-------------
1.67           | 2.50

In the real world application, the SQL is being dynamically generated based on user input on a report, and due to the complexities of the real world scenario I can't just re-write the query like this:

SELECT ROUND("'math'", 2) as "'math'", ROUND("'english'", 2) as "'english'"
  FROM TBL_EXAMPLE 
 PIVOT ( AVG(enrolled) FOR course IN ('math', 'english') );

So, my question is, is there any workaround I can use to bypass ORA-56902 in this scenario, or any other way to 'trick' Oracle into NOT returning up to 38 digits of decimal precision when numbers don't divide evenly via the AVG() calculation in a PIVOT clause?

Answer 1

Maybe I'm missing something, but why not perform the AVG() in a subquery with a ROUND and then apply your PIVOT:

select *
from
(
  select round(avg(enrolled), 2) enrolled, course
  from tbl_example
  group by course
) d
PIVOT 
( 
  max(enrolled) 
  FOR course IN ('math', 'english') 
);

See SQL Fiddle with Demo