可以使用条件聚合：

select id, name,
       max(case when seqnum = 1 then action end) as action_1,
       max(case when seqnum = 1 then date end) as date_1,
       max(case when seqnum = 2 then action end) as action_2,
       max(case when seqnum = 2 then date end) as date_2,
       max(case when seqnum = 3 then action end) as action_3,
       max(case when seqnum = 3 then date end) as date_3
from (select t.*, row_number() over (partition by id, name order by date) as seqnum
      from t
     ) t
group by id, name;

您还可以使用pivot query做一些简短的操作：

SELECT *
  FROM (SELECT dat.*, row_number() over(partition by id, name order by "DATE") rn FROM dat)
pivot 
(
   MAX(action) AS c,
   MAX("DATE") AS d
   FOR rn IN (1,2,3)
)

你可以用 row_number() 和条件聚合：

select
    id,
    name,
    max(case when rn = 1 then action end) c1,
    max(case when rn = 1 then date end)   d1,
    max(case when rn = 2 then action end) c2,
    max(case when rn = 2 then date end)   d2,
    max(case when rn = 3 then action end) c3,
    max(case when rn = 3 then date end)   d3
from (
    select 
        t.*, 
        row_number() over(partition by id, name order by date) rn
    from mytable t
) t
group by id, name