Create columns taking successive values from another column

each new column must take the value of the date corresponding to the message and reproduce it for each rnk_id group.

Each new column mess1, mess2, mess3, etc... takes the value of the column message. For column 1, I take the date that corresponds to mess1 (2022-12-19 10:48:51.5470000) and I copy it for each group rnk_id, for the second column I take the value mess 2 and I take the date of mess2 (2022-12-19 10:57:13.4230000) and I copy it for each group rnk_id... and so on

expected output :

for the first column no worries: first_value(date) OVER (PARTITION BY rnk_id ORDER BY date) as mess1

I am unable to use the ROWS Clause to achieve this

with cte and using first_value() and lead() we get the first row correctly then we spread this row over all the rows :

with cte as (
      select t.*, 
        first_value(case when message = 'mess1' then date end)
          over (partition by rnk_id order by date) as mess1,
        lead(date)
          over (partition by rnk_id order by date) as mess2,
        lead(date,2)
          over (partition by rnk_id order by date) as mess3
      from mytable t
    )
    select date, message, rnk_id, mess1,
      first_value(mess2) over (partition by rnk_id order by date) as mess2,
      first_value(mess3) over (partition by rnk_id order by date) as mess3
    from cte
    order by rnk_id desc

Demo here