Create columns taking successive values from another column

Question

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

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 :

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

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

I have read this a few times and I can't for the life of me figure out what you are trying to do. — Sean Lange, Feb 23 '23 at 20:05
What is the "ROWS clause"? Do you mean by limiting the window with something like `ROWS BETWEEN 1 PRECEEDING AND CURRENT ROW`? — Thom A, Feb 23 '23 at 20:07
Could you just add full expected output, with some more explanation? — Stu, Feb 23 '23 at 20:14
This question is hard to understand. Here are some hints how to make it clearer: For a column with a date in it (the first column) make the dates different. Having all the same day with just different seconds or mili-seconds makes it very hard to compare and understand expected output. The same is true for rnk_id -- unless the expectation is they are all the same make them different so we can see how that data impacts the results. — Hogan, Feb 23 '23 at 21:50

score 1 · Accepted Answer · answered Feb 23 '23 at 22:05

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

score -1 · Answer 2 · answered Feb 23 '23 at 21:42

You could pivot it but you lose the leading date and message columns in the result.

Fiddle

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

Result

rnk_id	mess1	mess2	mess3	mess4	mess5	mess6	mess7
5	2022-12-19 11:17:55.2360000	2022-12-19 11:18:02.6220000	2022-12-19 11:20:08.4910000	2022-12-19 11:20:19.2010000	2022-12-19 11:20:37.1190000	2022-12-19 11:20:40.2950000	2022-12-19 11:22:12.6280000
8	2022-12-19 10:48:51.5470000	2022-12-19 10:57:13.4230000	2022-12-19 10:57:23.6540000	2022-12-19 10:57:49.1290000	2022-12-19 10:57:58.0850000	2022-12-19 10:58:07.0980000	2022-12-19 11:00:36.0690000

unfortunately this is not the result I'm looking for, it's more like this but I can't manage to do it for intermediate values. https://dbfiddle.uk/wu9yAfy5 SELECT * ,first_value(_date) OVER (PARTITION BY rnk_id ORDER BY _date) as mess1 ,first_value(_date) OVER (PARTITION BY rnk_id ORDER BY _date DESC) as mess7 FROM mess ORDER BY _date ASC — leerickx, Feb 23 '23 at 21:54

Create columns taking successive values from another column

2 Answers2