I have a SQL table with many rows and columns. I need to find which column has all null values.
Assuming below is my sample table

In this case how do i get Column B as the final result in table like below without having to create it.

I also need to perform other types of validation like columns with duplicates, etc and hence will be appending the final result further

I tried with select count(column A), count(column B) from table and got the below

but I am stuck with how to retrieve the result in above format.

CodePudding user response：

There are more elegant ways to do this if all you wanted to do is find columns that have all nulls. Since you mention that you'll be checking other rules such as whether or not there are duplicates, union all may be the way to go. Express each data quality check as a rule, one per line. Use union all to create a table. Finally, wrap those checks in an outer select that has a where clause that filters out rows where the rule returns null instead of an error.

create or replace table T1(COL_A int, COL_B int);
insert into T1(COL_A, COL_B) values (1, null), (2, null), (1, null);

select * from
(
select iff(count(COL_A)=0,'All-Null',null) as ERROR, 'COL_A' as COLUMN_NAME from T1
    union all
select iff(count(COL_B)=0,'All-Null',null), 'COL_B' from T1
    union all
select iff(count(COL_A)<>count(distinct COL_A), 'Has-Duplicates', null), 'COL_A' from T1
    union all
select iff(count(COL_B)<>count(distinct COL_B), 'Has-Duplicates', null), 'COL_B' from T1
)
where ERROR is not null
;

If this is a particularly wide table or you have to reproduce these data quality checks across multiple tables, you could write a stored procedure that constructs the SQL dynamically by reading from the information_schema, runs the SQL and returns the resulting table.

If you need help writing an SP like this, let me know.