Transpose Rows to Columns Dynamically in MySQL – Page Rank Per User

I have the following data frame. I want to create a new table with the 4 most viewed pages per user (page and number of times viewed).

So, I need to transpose the following columns: page, quantity and rank.

Note that the variable to order is the rank and not necessarily all the names were in all the pages. Necessarily there must be 4 columns.

drop table sessions;
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);


id name page quantity rank
1. joan home 15 1
1. joan search 8 2
1 joan vip 4 3
1 joan checkout 2 4
2 fill home 20 1 
2 fill vip 2 2
3 mery search 10 1
3 mery checkout 7 2
3 mery home 5 3

Final desired output.

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       
3   mery  search   10        checkout  7         home  5

Answer

You can use conditional aggregation:

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

Demo on DB Fiddle:

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