CODEWITHSUNDEEP
sql-serverssisetl
Jan
Jan

Reputation: 41

Combining multiple rows into one in SSIS

I currently have a flat file that contains 4 columns: GateNumber, Status, DateTime and Priority. When the status number is 1, the gate opens. Status number 2 closes the gate. The file looks like this:

| 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

Is there a way in to combine these rows into one in a data flow in SSIS? The end result would be something like this:

| 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

Upvotes: 4

Views: 2600

Answers (2)

KeithL
KeithL

Reputation: 5594

This is a round about way of identifying the correct start and enddates. I am surprised there isn't a better identifier than gate number in your source. If gatenumber were unique and not repeating I would use an aggregate object and choose both max and min of datetime as your start and end.

Proposed Solution:

Step 1: Load your Source data using whatever the source type is

Step 2: Sort your data based on gatenumber then DateTime.

This will put your rows in order so that it will have a 1:2 1:2 sequence

Step 3: Add a script component (transformation)

Step 4: Create a new output that matches your need. I called it "new"

Step 5: Check all columns for input as read only

Step 6: The Code below

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;
    }


}

Note: public DateTime starttime is declared outside of the RowProcessing.

Step 7: Choose the correct Output and continue on

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use row_number() function to assign ranking and group them by GateNumberand 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

Note : This assumes you have always GateNumber within pair of status 1,2

Upvotes: 0

Related Questions