[SQL] Merge rows in a specific sequence in SQL (Azure SQL)

Here is my sample data set:

ID Timestamp Duration State
17 05.11.2024 00:00 0 Auto
16 04.11.2024 23:59 0 Auto
15 04.11.2024 23:58 1,47 Auto
14 04.11.2024 23:52 5,72 Manual
13 04.11.2024 23:52 0,13 Productive
12 04.11.2024 23:52 0,5 Error
11 04.11.2024 23:50 1,83 Auto
10 04.11.2024 23:50 0,08 Productive
9 04.11.2024 23:48 1,67 Auto
8 04.11.2024 23:48 0,12 Productive
7 04.11.2024 23:46 1,57 Auto
6 04.11.2024 23:46 0,17 Productive
5 04.11.2024 23:44 2,08 Auto
4 04.11.2024 23:44 0,1 Productive
3 04.11.2024 23:42 1,67 Auto
2 04.11.2024 23:42 0,13 Productive
1 04.11.2024 23:40 1,83 Auto

My target table should be this one:

ID Timestamp Duration State
9 05.11.2024 00:00 0 Auto
8 04.11.2024 23:59 0 Auto
7 04.11.2024 23:58 1,47 Auto
6 04.11.2024 23:52 5,72 Manual
5 04.11.2024 23:52 0,13 Productive
4 04.11.2024 23:52 0,5 Error
3 04.11.2024 23:50 1,83 Auto
2 04.11.2024 23:42 7,59 Productive
1 04.11.2024 23:40 1,83 Auto

I would like all lines to be merged as soon as the status Productive and then alternately Auto - Productive appears. The duration is totalled in the process. How can I realise this with an SQL query? I've been thinking about this for days and unfortunately I can't find the right solution. I have already tried using CTE to create and group specific sequences. Unfortunately without success so far. So I'm asking for help!! Maybe someone has the right idea.

Many thanks in advance!

Continue reading...