MySQL query return column names in comma separated list where value is null

Question

I want to return for each row, id and a comma separated list of column names that value is null/empty.

e.g. table name applicants

id  |  name  |  age  |  location
1   |  Matt  |  null |   london
2   |  Luis  |   24  |   paris 
3   |  null  |  null |   germany
4   |  James |   28  |   null

So the query results would look like:

id| missing
1 | age
3 | name,age
4 | location 

I know how to return the rows but not the column names in a comma separated list/.

SELECT * FROM `applicants` WHERE name='' OR age='' OR location='' 

How do i solve?

Answer 1

Use CASE expressions to check for nulls and CONCAT_WS to concatenate the results:

SELECT id,
       CONCAT_WS(
         ',',
         CASE WHEN name IS NULL THEN 'name' END,
         CASE WHEN age IS NULL THEN 'age' END,
         CASE WHEN location IS NULL THEN 'location' END
       ) missing  
FROM applicants
WHERE name IS NULL OR age IS NULL OR location IS NULL 
ORDER BY id;

See the demo.