Skip to content
Advertisement

Count of 300 non-null columns in each row

I have a table that contains more than 300 columns. Each column is of different type. I want the count of non-null columns of that table. Since the columns are more than 300, i should not type all the column names.

For example:

Result:

What is the best way to get the count and also to avoid the count of columns with empty spaces?

Advertisement

Answer

You can use CASE or NVL2 combined with TRIM:

(Note, if some columns have a non-string data-type then you can remove the call to TRIM for that column and just use CASE or NVL directly on the column.)


Update

You can use a PL/SQL program to generate the SQL:

Which for an example table with 30 columns:

Outputs:

db<>fiddle here

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement