Can I replace nulls in a PIVOT with zeroes

oracle-11g-r2pivot

I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes? I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am curious if there is a shortcut.

Best Answer

If you have the following table of data that you want to pivot:

CREATE TABLE yourtable (id int, name varchar2(1), value varchar(10));

INSERT ALL 
    INTO yourtable (id, name, value )
         VALUES (1, 'A', '1500')
    INTO yourtable (id, name, value )
         VALUES (1, 'B', '4500')
    INTO yourtable (id, name, value )
         VALUES (2, 'C', '3.5')
    INTO yourtable (id, name, value )
         VALUES (3, 'B', 'test')
    INTO yourtable (id, name, value )
         VALUES (4, 'A', 'blah')
    INTO yourtable (id, name, value )
         VALUES (4, 'C', 'hello')
SELECT * FROM dual;

And your current code is similar to:

select id, OptionA, OptionB, OptionC
from
(
  select id, name, value
  from yourtable
) src
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv

See SQL Fiddle with Demo. The sample data will create null values.

When you want to replace the null values you have to do it in the final SELECT list. So your code will need to be:

select id, 
  coalesce(OptionA, '0') OptionA, 
  coalesce(OptionB, '0') OptionB, 
  coalesce(OptionC, '0') OptionC
from
(
  select id, name, value
  from yourtable
)
pivot
(
  max(value)
  for name in ('A' as OptionA, 'B' as OptionB, 'C' as OptionC)
);

See SQL Fiddle with Demo

Related Question