您可以：

select 
  id,
  max(first_name) as first_name,
  max(last_name) as last_name,
  max(case when rn = 1 then preference end) as primary_method,
  max(case when rn = 2 then preference end) as secondary_method
from (
  select t.*, row_number() over(partition by id) as rn
  from t
) x
group by id;

结果：

ID   FIRST_NAME   LAST_NAME   PRIMARY_METHOD   SECONDARY_METHOD  
 ---- ------------ ----------- ---------------- ------------------ 
  1    John         Smith       Text             Mail              
  2    Anne         Doe         Twitter          null

请参阅db<>fiddle上的运行示例。我添加了一个额外的行（Anne）来显示分组。