sql - 在oracle中一行显示多行数据

Question

我的表中每个名称都有 3 个条目以及操作/日期，如图所示

 id  | Name| Action    |  Date 
 1   | abc |  Insert   |  01-02-2020 
 1   | abc |  Edit     |  02-02-2020  
 1   | abc |  Delete   |  02-06-2020
 2   | xyz |  Insert   |  02-06-2020
 2   | xyz |  Edit     |  05-06-2020
 2   | xyz |  Delete   |  05-06-2020

我想将数据显示为

ID  | Name | C1    |      D1     |   C2      |     D2        |    C3     |   D3
1   | abc  | Insert|  01-02-2020 |  Edit     |  02-02-2020   |  Delete   |  02-06-2020
2   | xyz  | Insert|  02-06-2020 |  Edit     |  05-06-2020   |  Delete   |  05-06-2020

Answer 1

您可以使用条件聚合:

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;