How can I count the number of columns in a row that are not null?
Assuming your table looks something like this:
id column1 column2 column3 ... 187 2 9 null 456 null 37 42 937 7 12 4 ...
The real answer is: you shouldn't need to. The answer to the question you asked is:
select case when column1 is null
then 0 else 1 end
+case when column2 is null
then 0 else 1 end
+case when column3 is null
then 0 else 1 end
+...
as not_null_columns
from TableA
where id = 937
Any time you find yourself wanting to do something to "all columns in a row" like this, step back and consider redesigning the table. There's a good chance the table isn't in first normal form. The main symptom is finding yourself writing clumsy queries with a lot of repetitive expressions.
A normalized one-to-many table would look something like this:
id columnx columnvalue 187 1 2 187 2 9 456 2 37 456 3 42 937 1 7 937 2 12 937 3 4 ...
Now the query gets a lot simpler:
select count(columnx) as not_null_columns from TableB where id = 937
Much nicer, wouldn't you agree?
Oracle White Papers: Fusion Middleware