Converting stored events into single records

Question

I am currently working on a project that needs to utilize data stored in a SQL db in the format shown below.

What I need to do, and am having difficulty finding a tool to help with, is to take this data and create a multi column single record keyed off of the ID and Event Date. While I have found some info on the net about event driven handlers, the problem is that although my labels say "Event" what is really stored is individual pieces of data called "Events" that are really individual entries that often, but not always, are entered at the same time and contribute to a whole event entry. I am sorry if this seems a bit confusing but I am working with a system I have no control over to change I just need to make useful sense of the data.

Here is a running example of what is stored in the db that I need to convert to a single record;

ID      Event           Event Result  Event Result Value            Event Date      Entered By
909909  Status Change       No          no                          10/25/12 23:10  Doe, John
909909  Disposition         Remuni      Remains in place            10/25/12 23:10  Doe, John
909909  Event duration      15mins      15mins                      10/25/12 23:10  Doe, John
909909  Event end time      23:25       23:25                       10/25/12 23:10  Doe, John
909909  Event start time    23:10       23:10                       10/25/12 23:10  Doe, John
909909  Event type          Iniqrs      Initial System activation   10/25/12 23:10  Doe, John

Into the following single record;

ID      Event Start Event End   Moved               Action Taken                Entered By
909909  23:10       23:25       Remains in place    Initial System activation   Doe, John

Thank you in advance for any help you can give.

Answer 1

While I agree with Bailey S. that Postgres is 'the best database engine on earth', I also see that you are using SQL Server.

The MSSQL function you need is called a 'Pivot' function and you can find the documentation here.

It can be a bit tricky to set up initially, but it is totally doable and will give you the results you are looking for.

---

Alternatively, there is a technique called a 'self join' and it looks like this:

SELECT
    [T1].[ID],
    [StatusChange].[EventResultValue] AS 'StatusChange',
    [Disposition].[EventResultValue] AS 'Disposition',
    ...
FROM
    (
        SELECT DISTINCT
            [EventsTable].[ID]
        FROM
            [EventsTable]
    ) AS [T1] LEFT OUTER JOIN
    [EventsTable] AS [StatusChange] ON [T1].[ID] = [StatusChange].[ID] AND [StatusChange].[Event] = 'Status Change' LEFT OUTER JOIN
    [EventsTable] AS [Disposition] ON [T1].[ID] = [Disposition].[ID] AND [Disposition].[Event] = 'Disposition' LEFT OUTER JOIN
    ...

A bit of a warning:
If you use this technique it will call the table each time it is listed in the FROM clause. There could be performance penalties it you need several columns or if the table has a large number of records.

---

Another option is to use a group & case statement together:

SELECT
    [ID],
    MAX(CASE WHEN [Event] = 'Status Change' THEN [EventResult] ELSE NULL END) AS [Event Start],
    MAX(CASE WHEN [Event] = 'Disposition' THEN [EventResult] ELSE NULL END) AS [Event End]
    ...
FROM
    [EventsTable]
GROUP BY
    [ID]

A bigger bit of warning:
If you use this technique it is guaranteed to return only one record per [ID]. If you have multiple Events of the same type (i.e. the Event start time could appear twice for any given ID, then do not use this as some of those records will not be returned.

Answer 2

There's a trick I like to break out for scenarios like this. The idea is that you want to group your data down to one id. If you use MAX() in conjuction with a CASE you can then basically pick a value from a desired row in that group and turn it into a column in the result set.
Here's the query:

select
  id,
  max(case when Event = 'Event start time' then [Event Result Value]
    else null end) as [Event Start],
  max(case when Event = 'Event end time' then [Event Result Value]
    else null end) as [Event End],
  max(case when Event = 'Disposition' then [Event Result Value]
    else null end) as [Moved],
  max(case when Event = 'Event type' then [Event Result Value]
    else null end) as [Action Taken],
  max([Entered By]) as [Entered By]
from Table1
group by id, [Event Date]

And here you can see it working in action.

Keep in mind that this assumes certain constraints. Namely, that for each id, [Event Date] combination there is only:

• one unique [Entered By] value
• one row with Event = 'Event start time'
• one row with Event = 'Event end time'
• one row with Event = 'Disposition'
• one row with Event = 'Event Type'

Note: This query is grouping by Event Date. This means that a record with the same date but different time will be considered as part of a separate group. This will work as desired if you have two sets of records with timestamps of the same day but two different times. On the flip side, you might get burned by having a record excluded from its intended group if its timestamp is slightly off. You just need to make sure that your groups have consistent timestamps.