how to concatenate multiple columns in oracle sql into single column only for matching condition

Question

i have below 4 columns

      empid | name  | dept     | ph_no
      ---------------------------------
      123   | null  |  null    | null
      124   | mike  |  science | null
      125   | null  |  physics | 789
      126   | null  |  null    | 463
      127   | john  |  null    | null

and i need to merge all 4 columns into single columns only for null values. And i need something like below--

    empid
 ------------
  123 is missing name,dept,ph_no
  124 is missing ph_no
  125 is missing name
  126 is missing name,dept
  127 is missing dept,ph_no

Answer 1

This can be done with case expressions.

select empid,empid||' is missing '|| 
trim(',' from 
     (case when name is null then 'name,' else '' end||
      case when dept is null then 'dept,' else '' end||
      case when ph_no is null then 'ph_no' else '' end
     )
     ) 
from tbl