在SSIS中将多行合并为一行

Question

我目前有一个扁平文件，包含4列： GateNumber，Status，DateTime和Priority。当状态编号为1时，门打开。状态编号2关闭门。该文件如下所示：

| GateNumber | Status | DateTime          | Priority
---------------------------------------------------------
| 53         | 1    | 2017-07-23 16:00:00 | 2
| 53         | 2    | 2017-07-23 16:01:30 | 2
| 87         | 1    | 2017-07-23 16:03:30 | 3
| 113        | 1    | 2017-07-23 16:04:40 | 2
| 113        | 2    | 2017-07-23 16:05:30 | 2
| 87         | 2    | 2017-07-23 16:07:30 | 3
| 53         | 1    | 2017-07-23 16:09:00 | 2
| 53         | 2    | 2017-07-23 16:09:30 | 2

在SSIS中，是否有一种方法可以将这些行合并成一个数据流？最终结果应该是这样的：

| GateNumber | StartDtm            | EndDtm          | Priority
-------------------------------------------------------------------
| 53         | 2017-07-23 16:00:00 | 2017-07-23 16:01:30 | 2
| 87         | 2017-07-23 16:03:30 | 2017-07-23 16:07:30 | 3
| 113        | 2017-07-23 16:04:40 | 2017-07-23 16:05:30 | 2
| 53         | 2017-07-23 16:09:00 | 2017-07-23 16:09:30 | 2

- Jan

---

它总是成对出现的吗？ - Squirrel

@Jan - 如果这些答案对您有帮助，请点赞或标记为已回答。 - KeithL

Answer 1

这是一种识别正确的开始和结束日期的方法，以航站楼门编号作为唯一识别符感到惊讶，源数据中没有更好的标识符。如果航站楼门编号是唯一且不重复的，则可以使用聚合对象，并选择datetime的最大值和最小值作为开始和结束时间。

建议解决方案：

步骤1：使用源类型加载您的源数据

步骤2：根据航站楼门编号和DateTime对数据进行排序。

这将按顺序排列行，使其具有1:2 1:2序列

步骤3：添加脚本组件（转换）

步骤4：创建与您需求相匹配的新输出。我称之为“new”

步骤5：检查所有列的输入是否为只读

步骤6：如下所示的代码

public DateTime starttime;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.Status == 1)
        starttime = Row.DateTime;
    else
    {
        newBuffer.AddRow();
        newBuffer.GN = Row.GateNumber;
        newBuffer.Start = starttime;
        newBuffer.End = Row.DateTime;
        newBuffer.priority = Row.Priority;
    }


}

注意：公共的DateTime starttime在RowProcessing之外声明。

步骤7：选择正确的输出并继续。

- KeithL

Answer 2

使用row_number()函数为GateNumber和row_numbers分配排名并对它们进行分组。

select a.GateNumber,
       min(case when a.status = 1 then a.Datetime end)  StartDtm,
       max(case when a.status = 2 then a.Datetime end) EndDtm,
       max(Priority) Priority from
  (
    select *, 
          row_number() over (partition by GateNumber, Status order by Datetime) rn 
    from table
  )a 
group by a.GateNumber, a.rn

注意：这假定你总是在状态 1,2 对中有 GateNumber。

- Yogesh Sharma

---

门号的重复使用是一个问题。 - KeithL