Display data from multiple row in one row in oracle

Admin 1
Viewed 34

I have 3 entries for every name along with action/date in my table as shown

 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

I want to show the data as

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
pivot oracle11g oracle sql
3 Answers

You can use conditional aggregation:

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;
user_11440350

You can also do a little shorter using 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)
)
user_102231470

You can use row_number() and conditional aggregation:

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
user_106767160
Related