I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like:
Select (non null columns) from tablename; I want to select all columns which have at least one non-null value.
Can this be done?
Have a look as statistics information, it may be useful for you:
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP'); PL/SQL procedure successfully completed. SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP'; NUM_ROWS ---------- 14 SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns 2 where owner='SCOTT' and table_name='EMP' order by column_id; COLUMN_NAME N NUM_DISTINCT NUM_NULLS ------------------------------ - ------------ ---------- EMPNO N 14 0 ENAME Y 14 0 JOB Y 5 0 MGR Y 6 1 HIREDATE Y 13 0 SAL Y 12 0 COMM Y 4 10 DEPTNO Y 3 0 8 rows selected. For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.
select column_name from user_tab_columns where table_name='Table_name' and num_nulls=0; Here is simple code to get non null columns..
Use the below:
SELECT * FROM information_schema.columns WHERE table_name = 'Table_Name' and is_nullable = 'NO' Table_Name has to be replaced accordingly...
information_schema is available in MySQL, but not Oracle database. The question is specific to Oracle database. This may help people who are looking for answers related to MySQL, but it's not particularly relevant to the question.I don't think this can be done in a single query. You may need some plsql to first test what columns contain data and put together a statement based on that information. Of course, if the data in your table changes you have to recreate the statement.
declare l_table varchar2(30) := 'YOUR_TABLE'; l_statement varchar2(32767); l_test_statement varchar2(32767); l_contains_value pls_integer; -- select column_names from your table cursor c is select column_name ,nullable from user_tab_columns where table_name = l_table; begin l_statement := 'select '; for r in c loop -- If column is not nullable it will always contain a value if r.nullable = 'N' then -- add column to select list. l_statement := l_statement || r.column_name || ','; else -- check if there is a row that has a value for this column begin l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' || r.column_name || ' is not null)'; dbms_output.put_line(l_test_statement); execute immediate l_test_statement into l_contains_value; -- Yes, add column to select list l_statement := l_statement || r.column_name || ','; exception when no_data_found then null; end; end if; end loop; -- create a select statement l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table; end; This block determines all columns in the table, loops through them in dynamic SQL and checks if they are null, then constructs a DBMS output query of the non-null query.
All you have to do is run the returned query.
I've included the exclusion of PKs and BLOB columns. Obviously, this is quite slow as going through columns one by one, and it's not going to be great for very hot tables, as data may change too quickly, but this works for me as I control traffic in dev env.
DECLARE l_table_name VARCHAR2(255) := 'XXXX'; l_counter NUMBER; l_sql CLOB; BEGIN FOR r_col IN (SELECT * FROM user_tab_columns tab_col WHERE table_name = l_table_name AND data_type NOT IN ('BLOB') AND column_name NOT IN (SELECT column_name FROM user_cons_columns con_col JOIN user_constraints cons ON con_col.constraint_name = cons.constraint_name AND con_col.table_name = cons.table_name WHERE con_col.table_name = tab_col.table_name AND constraint_type = 'P') ORDER BY column_id) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_table_name||' WHERE '||r_col.column_name||' IS NOT NULL' INTO l_counter; IF l_counter > 0 THEN IF l_sql IS NULL THEN l_sql := r_col.column_name; ELSE l_sql := l_sql||','||r_col.column_name; END IF; END IF; END LOOP; l_sql := 'SELECT '||l_sql||CHR(10) ||'FROM '||l_table_name; ---------- DBMS_OUTPUT.put_line(l_sql); END; 
select rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') col from (select column_name from user_tab_columns where table_name='<table_name>' and low_value is not null) 
What you're asking to do is establish a dependency on each row in the whole result. This is in fact not ever what you want. Just think of the ramifications if in one row every column had a value of '0' -- suddenly the schema of your result set grows to include all of those previously "empty" columns. You're effectively growing the badness of '*' exponentially, now your result set is not dependent on just the table's meta-data -- but your whole result set is dependent on the plain data.
What you want to do is just select the fields that have what you want, and not deviate from this simple plan.
