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:

 Column Id  |  Column1   |  Column2 | Column3 |   Column4  
------------+------------+----------+---------+-------------
   1        |    123     |    abc   |   NULL  |  Empty-space
 
   

Result:

ID | (Non-null)Column-Count
---+-----------------------   
1  |           2

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

Answer

You can use CASE or NVL2 combined with TRIM:

SELECT   CASE WHEN TRIM( column1 ) IS NULL THEN 0 ELSE 1 END
       + CASE WHEN TRIM( column2 ) IS NULL THEN 0 ELSE 1 END
       + CASE WHEN TRIM( column3 ) IS NULL THEN 0 ELSE 1 END
       + CASE WHEN TRIM( column4 ) IS NULL THEN 0 ELSE 1 END
       ...
       or
       ...
       + NVL2( TRIM( column5 ), 1, 0 )
       + NVL2( TRIM( column6 ), 1, 0 )
       + NVL2( TRIM( column7 ), 1, 0 )
       + NVL2( TRIM( column8 ), 1, 0 )

(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:

DECLARE
  v_sql CLOB;
  v_table_name VARCHAR2(20) := 'TABLE_NAME';
BEGIN
  FOR col IN ( SELECT column_name, data_type
               FROM   user_tab_columns
               WHERE  table_name = v_table_name
               ORDER BY column_id )
  LOOP
    IF v_sql IS NULL THEN
      v_sql := 'SELECT ';
    ELSE
      v_sql := v_sql || CHR(10) || '       + ';
    END IF;
    IF col.data_type IN ( 'CHAR', 'VARCHAR2' ) THEN
      v_sql := v_sql || 'NVL2( TRIM( ' || col.column_name || ' ), 1, 0 )';
    ELSE
      v_sql := v_sql || 'NVL2( ' || col.column_name || ', 1, 0 )';
    END IF;
  END LOOP;
  v_sql := v_sql || ' as cnt' || CHR(10) || 'FROM   ' || v_table_name || ';';
  DBMS_OUTPUT.PUT_LINE( v_sql );
END;
/

Which for an example table with 30 columns:

CREATE TABLE table_name ( col001 VARCHAR2(20) );

BEGIN
  FOR i IN 2 .. 30 LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE table_name ADD ( col' || TO_CHAR(i, 'FM000' )
                      || CASE MOD(i,3) WHEN 0 THEN ' NUMBER' ELSE ' VARCHAR2(20)' END
                      || ' )';
  END LOOP;
END;
/

Outputs:

SELECT NVL2( TRIM( COL001 ), 1, 0 )
       + NVL2( TRIM( COL002 ), 1, 0 )
       + NVL2( COL003, 1, 0 )
       + NVL2( TRIM( COL004 ), 1, 0 )
       + NVL2( TRIM( COL005 ), 1, 0 )
       + NVL2( COL006, 1, 0 )
       + NVL2( TRIM( COL007 ), 1, 0 )
       + NVL2( TRIM( COL008 ), 1, 0 )
       + NVL2( COL009, 1, 0 )
       + NVL2( TRIM( COL010 ), 1, 0 )
       + NVL2( TRIM( COL011 ), 1, 0 )
       + NVL2( COL012, 1, 0 )
       + NVL2( TRIM( COL013 ), 1, 0 )
       + NVL2( TRIM( COL014 ), 1, 0 )
       + NVL2( COL015, 1, 0 )
       + NVL2( TRIM( COL016 ), 1, 0 )
       + NVL2( TRIM( COL017 ), 1, 0 )
       + NVL2( COL018, 1, 0 )
       + NVL2( TRIM( COL019 ), 1, 0 )
       + NVL2( TRIM( COL020 ), 1, 0 )
       + NVL2( COL021, 1, 0 )
       + NVL2( TRIM( COL022 ), 1, 0 )
       + NVL2( TRIM( COL023 ), 1, 0 )
       + NVL2( COL024, 1, 0 )
       + NVL2( TRIM( COL025 ), 1, 0 )
       + NVL2( TRIM( COL026 ), 1, 0 )
       + NVL2( COL027, 1, 0 )
       + NVL2( TRIM( COL028 ), 1, 0 )
       + NVL2( TRIM( COL029 ), 1, 0 )
       + NVL2( COL030, 1, 0 ) as cnt
FROM   TABLE_NAME;

db<>fiddle here