Showing all column names where value is null-CodePudding

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.

CodePudding user response：

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;