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:
x
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?
Advertisement
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