Skip to content
Advertisement

SQL: Grouping Paired Rows Without Field to Aggregate On

So say I have the following:

Timestamp Event_Name
10:47:00 RESUME
10:37:52 SUSPEND
10:26:20 RESUME
09:48:27 SUSPEND
09:39:13 RESUME
09:15:38 SUSPEND
09:11:42 RESUME

I want to be able to pair them as such:

Suspend Resume
null 10:47:00
10:37:52 10:26:20
09:48:27 09:39:13
09:15:38 09:11:42

If each pair had another field to group on I would use a listagg window function but I can’t. Lag function doesn’t work either.

Advertisement

Answer

As having a DBMS containing window function, you can use the following query

where SUM window function is used to count the each switch for the Event_Name

Demo

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement