Showing all column names where value is null

user_17336710 0
Viewed 179

is there anyway i can show all column names where values are null? For example i have table like this:

id name surname
1 Jack NULL
2 NULL Grain
3 NULL NULL

And i want my result to look like that:

id nullFields
1 name
2 surname
3 name, surname

Perfect solution would be some sql which takes all the columns and check them (if there wouldnt be need to manually input column name) but if there is no such possibility "normal solution" will do fine.

mysql sql
1 Answers

We can use the base string functions here:

SELECT id, CONCAT_WS(', ',
                     CASE WHEN name IS NULL THEN 'name' END,
                     CASE WHEN surname IS NULL THEN 'surname' END) AS nullFields
FROM yourTable
ORDER BY id;
user_18632290
Related