我想你可以做到：

select split(datalist, '_')[1],
       (case when sum(case when datalist like '%NEW' then 1 else 0 end) > 0 and
                  sum(case when datalist like '%OLD' then 1 else 0 end) > 0
             then 'BOTH'
             when sum(case when datalist like '%NEW' then 1 else 0 end) > 0 
             then 'NEW ONLY'
             else 'OLD ONLY'
         end)

from t
group by split(datalist, '_')[1];