可以使用条件聚合。我想这就是你想要的逻辑：

select cif_id, staff_id, package, segment,
       max(case when seqnum = 1 and primary_card_flag = 'Y' then card_number end),
       max(case when seqnum = 1  and primary_card_flag = 'N' then card_number end),
       max(case when seqnum = 1 and primary_card_flag = 'Y' then valid_from_date end),
       max(case when seqnum = 1 and primary_card_flag = 'N' then valid_from_date end)
from (select t.*,
             row_number() over (partition by cif_id, staff_id, package, segment, primary_card_flag
                                order by valid_from_date desc
                               ) as seqnum
      from t
     ) t
group by cif_id, staff_id, package, segment order;