将存储的事件转换为单个记录

Question

我目前正在从事一个项目，该项目需要利用存储在SQL db中的数据，其格式如下所示。

我需要做的，而且我很难找到一个工具来帮助，是获取这些数据，并创建一个多列的单一记录，该记录与ID和事件日期相分离。虽然我在网上找到了一些关于事件驱动处理程序的信息，但问题是，虽然我的标签上写着“event ”,但实际上存储的是被称为“Events”的单独的数据片段，这些数据片段实际上是单独的条目，它们经常(但不总是)被同时输入，并构成一个完整的事件条目。我很抱歉，如果这似乎有点混乱，但我正在与一个系统，我无法控制改变，我只是需要使有用的数据意义。

下面是一个运行中的示例，我需要将数据库中存储的内容转换为单个记录；

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

进入以下单个记录；

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

提前感谢您的帮助。

Answer 1

有一个窍门我喜欢在这样的场景中爆发出来。这个想法是，您希望将数据分组为一个id。如果将MAX（）与CASE结合使用，则基本上可以从该组中所需的行中选择一个值，并将其转换为结果集中的列
以下是查询：

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]

在这里，你可以看到它的实际作用。

请记住，这假设了某些约束。也就是说，对于每个< code>id，[事件日期]组合，只有:

• 一个唯一的 [输入者] 值
• 事件 =“事件开始时间”的一行
• 事件 =“事件结束时间”的一行
• 事件 =“处置”的一行
• 事件 =“事件类型”的一行

注意:此查询按< code >事件日期分组。这意味着日期相同但时间不同的记录将被视为单独组的一部分。如果您有两组时间戳相同但时间不同的记录，这将按预期工作。另一方面，如果某个记录的时间戳稍有偏差，您可能会因为将该记录从其目标组中排除出去而遭受损失。您只需要确保您的组有一致的时间戳。

Answer 2

虽然我同意Bailey S.的观点，即Postgres是“世界上最好的数据库引擎”，但我也看到您正在使用SQL Server。

您需要的MSSQL函数称为Pivot函数，您可以在此处找到留档。

最初的设置可能有点棘手，但这是完全可行的，会给你带来你想要的结果。

或者，有一种叫做“自连接”的技术，它看起来像这样：

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
    ...

一点警告:< br >如果您使用这种技术，它将在每次表被列在< code>FROM子句中时调用该表。如果您需要几个列，或者如果表中有大量记录，可能会有性能损失。

另一种选择是使用组

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]

一个更大的警告：
如果您使用这种技术，它保证每个[ID]只返回一条记录。如果您有多个相同类型的事件（即任何给定ID的事件开始时间可能出现两次，那么不要使用它，因为其中一些记录将不会被返回。