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.

Answer

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

WITH t AS
( 
  SELECT CASE
           WHEN Event_Name = 'SUSPEND' THEN
            Timestamp
         END AS Suspend,
         CASE
           WHEN Event_Name = 'RESUME' THEN
            Timestamp
         END AS Resume,
         SUM(CASE
               WHEN Event_Name = 'SUSPEND' THEN
                1
               ELSE
                0
             END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_suspend,
         SUM(CASE
               WHEN Event_Name = 'RESUME' THEN
                1
               ELSE
                0
             END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_resume  
    FROM t
)
SELECT s.suspend, r.resume
  FROM (SELECT * FROM t WHERE resume IS NOT NULL) AS r
  FULL JOIN (SELECT * FROM t WHERE suspend IS NOT NULL) AS s
    ON s.rn_suspend = r.rn_resume
 ORDER BY r.rn_resume DESC, s.rn_suspend DESC

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

Demo