一个简单的选项使用 union all :

select employee_id, employee_name, location_1 location from mytable where location_1 is not null
union all 
select employee_id, employee_name, location_2 from mytable where location_2 is not null
union all 
select employee_id, employee_name, location_3 from mytable where location_3 is not null

使用Map和 lateral view explode 可能更有效：

select employee_id, employee_name, location
from (
    select 
        employee_id, 
        employee_name, 
        map("location_1", location_1, "location_2", location_2, "location_3", location_3) as mp
    from mytable
) t
lateral view explode(mp) m as locname, location  
where location is not null

可能最简单的方法就是 union all . 但这需要扫描表多次。因此，取而代之的是：

select tn.*
from (select t.employee_id, t.employee_name,
             (case when n.n = 1 then location_1
                   when n.n = 2 then location_2
                   when n.n = 3 then location_3
              end) as location 
      from t cross join
           (select 1 as n union all select 2 union all select 3) n
     ) tn
where location is not null;