I have a table like this:
time | ID | Page
9:30 | 1 | name1
9:30 | 1 | name2
9:30 | 1 | name3
9:30 | 2 | name4
9:30 | 2 | name5
7:30 | 3 | name1
And I want to use pivot table to change the table like below:
time | ID | Page1 | Page2 | Page3
9:30 | 1 | name1 | name2 | name3
9:30 | 2 | name4 | name5 | null
7:30 | 3 | name1 | null | null
please help me with that. Thx
Here you are:
with w(time, ID, Page) as
(
select '9:30', 1, 'name1' from dual
union all
select '9:30', 1, 'name2' from dual
union all
select '9:30', 1, 'name3' from dual
union all
select '9:30', 2, 'name4' from dual
union all
select '9:30', 2, 'name5' from dual
union all
select '7:30', 3, 'name1' from dual
)
select *
from
(
select w.time, w.id, w.page, row_number() over (partition by w.time, w.id order by w.page) rnk
from w
)
pivot (max(page) for rnk in (1 as page1, 2 as page2, 3 as page3))
order by 1, 2
;
This gives:
TIME ID PAGE1 PAGE2 PAGE3
7:30 3 name1
9:30 1 name1 name2 name3
9:30 2 name4 name5
