如何将oracle sql中的多个列串联为仅用于匹配条件的单个列 [英] how to concatenate multiple columns in oracle sql into single column only for matching condition

问题描述

我下面有4列

      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

，我只需要为空值就将所有4列合并为单列. 而且我需要以下类似的东西-

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

推荐答案

这可以通过case表达式完成.

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

这篇关于如何将oracle sql中的多个列串联为仅用于匹配条件的单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！