Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
NVL(column_name,0) ? –  Phil Mar 21 '13 at 16:58
    
There is no "column" name, per se, in the PIVOT function. This will work if I wrap the entire query; I am wondering if there is an alternative. –  JHFB Mar 21 '13 at 17:41

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.