Combine 2 rows into 1 row (Start and End times)

Question

I am not sure how to word this but I have Start and End times in separate rows, and I want to merge them into a single a row. In this sample data, it's basically tracking/logging project times:

Project  Type    Time
A        Start   1:00
A        End     1:10
B        Start   2:00
B        End     2:10
B        Start   2:30
B        End     2:45
C        End     3:00
D        Start   3:10
D        End     3:20

What I am looking for is something like this:

Project  Start    End
A        1:00     1:10
B        2:00     2:10
B        2:30     2:45
C        NULL     1:10
D        3:10     3:20

The 2 parts that are odd are that:

• I may have multiple start/end time pairs for the same project (such as Project B above), possibly back to back or separated on the same day
• I may have some missing start or end times.

Can anyone point me in the right direction? I couldn't find anything on Stackoverflow that had these same requirements.

Answer 1

This is some kind of gaps-and-island problem.

I would approach this with lag() and a window sum(). A new group starts everytime consecutive records types are not 'Start' followed by an 'End'.

select 
    project, 
    min(case when type = 'Start' then time end) Start,
    max(case when type = 'End' then time end) [End]
from (
    select
        t.*,
        sum(case when type = 'End' and lag_type = 'Start' then 0 else 1 end) 
            over(partition by project order by time) grp
    from (
        select
            t.*,
            lag(type) over(partition by project order by time) lag_type
        from mytable t
    ) t
) t
group by project, grp
order by project, grp

Demo on DB Fiddle:

Project | Start | End 
:------ | :---- | :---
A       | 1:00  | 1:10
B       | 2:00  | 2:10
B       | 2:30  | 2:45
C       | null  | 3:00
D       | 3:10  | 3:20