问题

下面是您要的翻译内容：

| date                | message | rnk_id | mess1               | mess2               | mess3               |
|---------------------|---------|--------|---------------------|---------------------|---------------------|
| 2022-12-19 10:48:51 | mess1   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 10:57:13 | mess2   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 10:57:23 | mess3   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 10:57:49 | mess4   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 10:57:58 | mess5   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 10:58:07 | mess6   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2022-12-19 11:00:36 | mess7   | 8      | 2022-12-19 10:48:51 | 2022-12-19 10:57:13 | 2022-12-19 10:57:23 |
| 2023-02-06 11:17:55 | mess1   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:18:02 | mess2   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:20:08 | mess3   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:20:19 | mess4   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:20:37 | mess5   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:20:40 | mess6   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |
| 2023-02-06 11:22:12 | mess7   | 5      | 2023-02-06 11:17:55 | 2023-02-06 11:18:02 | 2023-02-06 11:20:08 |

希望这可以满足您的需求。如果您有其他问题，请随时告诉我。

答案1

得分: 1

使用cte和first_value()以及lead()，我们正确获取第一行，然后将这一行扩展到所有行上：

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

演示在此处

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

答案2

得分: -1

你可以转换它，但是结果中会失去主日期和消息列。

[Fiddle][1]

SELECT * FROM
(
SELECT rnk_id,
message,
_date
FROM mess
) src
PIVOT
(
MAX(_date)
FOR message IN (
[mess1],
[mess2],
[mess3],
[mess4],
[mess5],
[mess6],
[mess7])
) pvt

结果

[1]: https://dbfiddle.uk/_lWLstcC

SELECT * FROM
(
SELECT rnk_id,
message,
_date
FROM mess
) src
PIVOT
(
MAX(_date)
FOR message IN (
[mess1],
[mess2],
[mess3],
[mess4],
[mess5],
[mess6],
[mess7])
) pvt