How do I get an overview of which row has a NULL value and whats the name of the column the NULL value is in

I have a table with translations, looking like:

table name: text

id  |  en   | de    | fr  
1   | test  | NULL  | test  
2   | thing | thing | NULL  
3   | other | NULL  | NULL

The en column is never null, I would like an overview of all column names whenever one or more of them have a NULL value. So from the table above, I would like this result:

test  | de  
thing | fr  
other | de | fr

Most ideally this would be done in a single query, but I don’t know if that is possible.

Currently I use a query to determine the NULL values from a single language, which is:

SELECT en FROM text WHERE de IS NULL

and do this manually for each language.

Any help or a step in the right direction would be appreciated.

Answer

A pretty simple method is:

select id,
       concat( (case when en is null then 'en;' else '' end),
               (case when de is null then 'de;' else '' end),
               (case when fr is null then 'fr;' else '' end)
             ) as missing_columns
from t
where en is null or de is null or fr is null;

Advertisement

Answer