Skip to content
Advertisement

Case expression for null and not null columns in a table

My table:

Query tried

Basically I want that if there is any single null bvalue for any column in table StudentScore, then the type of that column should be null else it should be not null (note that this is part of interview question and I cannot use information_schema etc. I need to do this using case. Can anyone help

For e.g. here ID will be NON-NULL, rest two will be type ‘At least 1 null’

EDIT after seeing answers to clarify:

I want that my code should check all rows of columns and return ‘Non-null’ if all the rows in a column are not null. All the columns should be checked individually.

For e.g. this code gives output as below:

The above is not my desired output.

My desired output is

Not null columns: ID ,At least 1 null value (in all the rows corresponding to one column): Student_Score, Student_name.

So the code should return ‘at least 1 null value’ if for a particular column, there is at least one null value present in all the rows .

For e.g. it should check all 8 rows corresponding to each column and if there is no null value in all rows corresponding to one column, then only that column is going to ‘Not null’

Also I removed primary key to make question more generic.

Advertisement

Answer

The simplest method is to put this into separate columns:

This should be the simplest and most performance way to do what you want.

If you want this in separate rows, I would just unpivot these results:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement