-1

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. enter image description here

How can I do this?

2

1 Answer 1

2

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:

enter image description here

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:

enter image description here

dbfiddle here

2
  • That's awesome, I was hoping it'd be a simple PIVOT and it was :) I just haven't used it much before. Thanks!! Sep 28, 2022 at 6:04
  • You are welcome!
    – Sabin B
    Sep 28, 2022 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.