sql

CREATE TABLE B
(
    User_Id     BIGINT,
    Item_Id     BIGINT,
    Searches    BIGINT,
    Clicks      BIGINT,
    Ctr         BIGINT,
    Item_Id_Num BIGINT

);

CREATE TABLE A
(
    User_Id  BIGINT,
    Item_Id  BIGINT,
    Searches BIGINT,
    Clicks   BIGINT
);

INSERT INTO A
    (User_Id, Item_Id, Searches, Clicks)
VALUES (1234, 1, 100, 11),
       (1234, 2, 20, 1),
       (1235, 1, 3, 2),
       (1235, 2, 3, 2),
       (1238, 1, 3, 2);

SELECT *
FROM A;



INSERT INTO B
SELECT User_Id,
       Item_Id,
       Searches,
       Clicks,
       Clicks / Searches                                             AS Ctr,
       ROW_NUMBER() OVER (PARTITION BY User_Id ORDER BY Item_Id ASC) AS Item_Id_Num
FROM A;

SELECT *
FROM B;

SELECT User_Id,
       MAX(CASE WHEN Item_Id_Num = 1 THEN Item_Id END) AS Item_Id1,
       MAX(CASE WHEN Item_Id_Num = 1 THEN Ctr END)     AS Ctr_Item1,
       MAX(CASE WHEN Item_Id_Num = 2 THEN Item_Id END) AS Item_Id2,
       MAX(CASE WHEN Item_Id_Num = 2 THEN Ctr END)     AS Ctr_Item2,
       MAX(CASE WHEN Item_Id_Num = 3 THEN Item_Id END) AS Item_Id3,
       MAX(CASE WHEN Item_Id_Num = 3 THEN Ctr END)     AS Ctr_Item3
FROM B
GROUP BY User_Id;