How to combine multiple rows into one, based on event id, datetime, and an action into their own columns?

Question

I'm not sure how to word the topic title...

I have a table with data similar to below, where each row stores an event associated with alarms. For example, events might include:

• alarm goes active
• alarm clears/inactive
• alarm is acknowledged by an operator

Each group of events is associated with a single eventid.

I want to combine all events for each group of the same eventid into a single row, using the eventtime as the value for each of the alarm events/actions: active, cleared, and acked.

source data:

eventid	eventtime	action
0203cfd5-3b82-4285-a830-447e8d5a63f7	2022-09-26 14:38:17.580	Cleared
eb5f5fa5-a1ef-4aa7-b412-779676e72b3b	2022-09-26 14:38:17.560	Active
eb5f5fa5-a1ef-4aa7-b412-779676e72b3b	2022-09-28 13:54:59.807	Ack
eb5f5fa5-a1ef-4aa7-b412-779676e72b3b	2022-09-28 13:55:55.437	Cleared

expected return:

eventid	active	cleared	acked
0203cfd5-3b82-4285-a830-447e8d5a63f7	null	2022-09-26 14:38:18	null
eb5f5fa5-a1ef-4aa7-b412-779676e72b3b	2022-09-26 14:38:18	2022-09-28 13:55:55	2022-09-28 13:55:00

E.g.

How can I do this?

Answer 1

You can use pivot or a combination of MAX + CASE

CREATE TABLE Table1
    ([eventid] varchar(36), [eventtime] datetime, [action] varchar(7))
;
    
INSERT INTO Table1
    ([eventid], [eventtime], [action])
VALUES
    ('0203cfd5-3b82-4285-a830-447e8d5a63f7', '2022-09-26 14:38:17', 'Cleared'),
    ('eb5f5fa5-a1ef-4aa7-b412-779676e72b3b', '2022-09-26 14:38:17', 'Active'),
    ('eb5f5fa5-a1ef-4aa7-b412-779676e72b3b', '2022-09-28 13:54:59', 'Ack'),
    ('eb5f5fa5-a1ef-4aa7-b412-779676e72b3b', '2022-09-28 13:55:55', 'Cleared');

pivot version

SELECT *
FROM Table1 AS t
PIVOT(
  MAX(EventTime) 
  FOR Action IN ([Active],[Cleared],[Ack])
) as p;

output:

Max + Case version

SELECT EventID,
       MAX(CASE WHEN Action = 'Active' THEN EventTime ELSE NULL END) as [Active],
       MAX(CASE WHEN Action = 'Cleared' THEN EventTime ELSE NULL END) as [Cleared],
       MAX(CASE WHEN Action = 'Ack' THEN EventTime ELSE NULL END) as [Ack]
FROM Table1 as t
GROUP BY EventID;

output:

dbfiddle here