EXPERT RESPONSE
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?
|
