How to combine rows into one row in SQL?

Question

I have a problem with complex requirements, I'm hoping I can get some help with solving this. My SQL knowledge is very basic, and I have no idea how to solve this problem. Currently, I have an Events table structured as follows:

ID     | Name  | Time                    | Event Type
----------------------------------------------------------
133000 | Elise | 2016-02-17 06:39:42.000 | Arrival
133000 | Elise | 2016-02-18 06:20:22.000 | Arrival
133000 | Elise | 2016-02-18 20:43:46.000 | Departure
133020 | Elise | 2016-02-19 06:29:46.000 | Arrival
133445 | Peter | 2016-02-01 20:09:00.000 | Departure
133445 | Peter | 2016-02-02 06:32:02.000 | Arrival
133445 | Peter | 2016-02-02 17:03:04.000 | Departure
133445 | Peter | 2016-02-02 19:44:06.000 | Arrival
133445 | Peter | 2016-02-02 19:56:56.000 | Departure

Now, I want to query this data in a way so that it is structured this way:

ID     | Name  | Arrival                 | Departure
----------------------------------------------------------
133000 | Elise | 2016-02-17 06:39:42.000 | NULL
133000 | Elise | 2016-02-18 06:20:22.000 | 2016-02-18 20:43:46.000
133000 | Elise | 2016-02-19 06:29:46.000 | NULL
133445 | Peter | NULL                    | 2016-02-01 20:09:00.000
133445 | Peter | 2016-02-02 06:32:02.000 | 2016-02-02 17:03:04.000
133445 | Peter | 2016-02-02 19:44:06.000 | 2016-02-02 19:56:56.000

In other words, I have two new columns: Arrival and Departure. Then for each person in the table, apply the following logic in chronological order:

• If the Event Type is Arrival, it should be mapped to a new row with the Time value in the Arrival column.
• If the Event Type is Departure, check to see if the previous row is also Departure. If so, it should be mapped to a new row with the Time value in the Departure column, and Arrival is null. If not, just transfer the Time value into the Departure column of the previous row.

It is best if that can be done via a SQL Query, but a function is fine too. I am using MS SQL Server. Thanks!

Answer 1

You can do this in various ways. One method is lead(), but you need to be careful:

select id, name, time as Arrival,
       (case when next_eventtype = 'Departure' then next_time end) as Departure
from (select e.*,
             lead(time) over (partition by id order by time) as next_time,
             lead(eventtype) over (partition by id order by time) as next_eventtype,
      from events e
     ) e
where eventtype = 'Arrival';

lead() is available in SQL Server 2012+. In earlier versions, you would use apply instead.

Answer 2

If your data always has correct amount of Arrival rows, Gordon Linoff's solution is probably better, but if the data is broken, you'll probably need to do more complex trickery with row_number, something like this:

select 
  Name, 
  max(case when [Event Type] = 'Arrival' then Time end) as Arrival,
  max(case when [Event Type] = 'Departure' then Time end) as Departure
from (
  select case when [Event Type] = 'Departure' and lag([Event Type]) over (partition by Name order by [Time] asc) = 'Arrival' then RN -1 else RN end as RN2, *
  from (
    select row_number() over (partition by Name order by [Time]) as RN, *
    from yourtable
  ) X
) Y
group by Name, RN2
order by Name, Arrival, Departure

This will assign all the rows with a row number, and in case the row is a departure, and the previous row is an arrival, it will deduct one from the row number -> those rows will have the same number. This number is then used to group the data, so all orphan rows will be displayed separately.