Q: 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.

A: It can be done, but to do it in pure SQL is messy and may not scale well if you add more columns.
It's also not clear how strict your requirement is for how the results should appear, which could add a lot of complexity.
select en,
       case when de is null then 'de' end as de,
       case wehn fr is null then 'fr' end as fr
  from text
 where coalesce(de, fr) is NULL

(If your database doesn't have coalesce - which I can't be sure since as far as I can see you didn't specify what DBMS - then you could replace the where clause as
where de is NULL or fr is NULL


A: You can have a column of comma separated values like this:
select
  en,
  case 
    when de is null and fr is null then 'de,fr'
    when de is null then 'de'
    when fr is null then 'fr'
  end nullcolumns
from text

The result will be:
test  | de  
thing | fr  
other | de,fr

If you want separate columns for de and fr:
select
  en,
  case 
    when de is null then 'de'
    when fr is null then 'fr'
  end colde,
  case 
    when de is null and fr is null then 'fr'
  end colfr
from text

The result will be:
test  | de | 
thing | fr | 
other | de | fr


A: 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;

