By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS sessions (
id int(11),
name varchar(10),
page varchar(10),
quantity int(11),
rank int(11)
);
insert into sessions values (1,'joan','home',15,1);
insert into sessions values (1,'joan','search',8,2);
insert into sessions values (1,'joan','vip',4,3);
insert into sessions values (1,'joan','checkout',2,4);
insert into sessions values (2,'fill','home',20,1);
insert into sessions values (2,'fill','vip',2,2);
insert into sessions values (3,'mery','search',10,1);
insert into sessions values (3,'mery','checkout',7,2);
insert into sessions values (3,'mery','home',5,3);
select
id,
name,
max(case when rank = 1 then page end) page1,
max(case when rank = 1 then quantity end) quantity1,
max(case when rank = 2 then page end) page2,
max(case when rank = 2 then quantity end) quantity2,
max(case when rank = 3 then page end) page3,
max(case when rank = 3 then quantity end) quantity3,
max(case when rank = 4 then page end) page4,
max(case when rank = 4 then quantity end) quantity4
from sessions
group by id, name
| id | name | page1 | quantity1 | page2 | quantity2 | page3 | quantity3 | page4 | quantity4 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | joan | home | 15 | search | 8 | vip | 4 | checkout | 2 |
| 2 | fill | home | 20 | vip | 2 | null | null | null | null |
| 3 | mery | search | 10 | checkout | 7 | home | 5 | null | null |