oracle sql pivot query table

问题:

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

回答1:

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