How to Select Columns from a Table Which Have Non Null Values

How to select columns from a table which have non null values?

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.

How to select only columns with not null values sql

A SQL query is fixed in the columns that are returned. So, you cannot do what you want with a simple select query. You could use dynamic SQL.

However, you can only return the values that you want as a single string:

select name, concat_ws(',', column1, column2, column3, column4, column5, column6)
from t
where . . . ;

That said, the real issue is probably your data structure. in general, you do not want to store such column values in a pivoted form. Instead, include a single value per row. In other words, you want a junction/association table with one row per value and per name.

Then the query would be:

select name, group_concat(value)
from association_table 
group by name;

SQL query to find columns having at least one non null value

I would not recommend using count(distinct) because it incurs overhead for removing duplicate values. You can just use count().

You can construct the query for counts using a query like this:

select count(col1) as col1_cnt, count(col2) as col2_cnt, . . .
from t;

If you have a list of columns you can do this as dynamic SQL. Something like this:

declare @sql nvarchar(max);

select @sql = concat('select ',
                     string_agg(concat('count(', quotename(s.value), ') as cnt_', s.value),
                      ' from t'
                    )
from string_split(@list) s;

exec sp_executesql(@sql);

This might not quite work if your columns have special characters in them, but it illustrates the idea.

select only those columns from table have not null values in q kdb

Here is a simple solution that does just what you want:

q)where[all null t]_t
a c
---
1 a
2 b
3 c

[all null t] gives a dictionary that checks if the column values are all null or not.

q)all null t
a| 0
b| 1
c| 0

Where returns the keys of the dictionary where it is true

q)where[all null t]
,`b

Finally you use _ to drop the columns from table t

Hopefully this helps

Select only not null column values and display only that values using sql query

You are looking for coalesce(), if you want the first non-NULL value:

select t.*, coalesce(col1, col2, col3, col4) as col5
from t;

how to select rows with no null values (in any column) in SQL?

You need to explicitly list each column. I would recommend:

select t.*
from t
where col1 is not null and col2 is not null and . . . 

Some people might prefer a more concise (but slower) method such as:

where concat(col1, col2, col3, . . . ) is not null

This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.

---

Related Topics