Non-aggregating pivot

  • I'm trying to write a query to turn this record set:

    eventtypeeventdatetime

    start 2016-05-17 08:00:00.000

    start 2016-05-17 09:10:00.000

    start 2016-05-17 09:40:00.000

    stop 2016-05-17 08:10:00.000

    stop 2016-05-17 09:12:00.000

    stop 2016-05-17 09:50:00.000

    into

    starttime endtime

    2016-05-17 08:00:00.000 2016-05-17 08:10:00.000

    2016-05-17 09:10:00.000 2016-05-17 09:12:00.000

    2016-05-17 09:40:00.000 2016-05-17 09:50:00.000

    and, of course, keeping them in sequential order.

    I know there are several ways to accomplish this, but I'm looking for something simple/elegant that can be used in a CTE (i.e.-no ORDER BY clause), if possible.

    Any thoughts?

  • Well, I guess I answered my own question.. unless someone can offer a better solution:

    with a as (

    select 'start' [eventtype],cast('2016-05-17 08:00:00' as datetime) [eventdatetime]

    union select 'start',cast('2016-05-17 09:10:00' as datetime)

    union select 'start',cast('2016-05-17 09:40:00' as datetime)

    union select 'stop',cast('2016-05-17 09:50:00' as datetime)

    union select 'stop',cast('2016-05-17 09:12:00' as datetime)

    union select 'stop',cast('2016-05-17 08:10:00' as datetime)

    union select 'start',cast('2016-05-17 09:40:00' as datetime)

    union select 'stop',cast('2016-05-17 09:50:00' as datetime)

    union select 'stop',cast('2016-05-17 09:12:00' as datetime)

    ), b as (

    select

    [eventdatetime]

    , [eventtype]

    , row_number() over (partition by [eventtype] order by [eventdatetime]) [rowId]

    from a

    ), c as (

    select

    (select [eventdatetime] from b as b2 where b2.[eventtype] = 'start' and b1.[rowId] = b2.[rowId]) as [startdate]

    , (select [eventdatetime] from b as b3 where b3.[eventtype] = 'stop' and b1.[rowId] = b3.[rowId]) as [stopdate]

    from b as b1

    where [eventtype] = 'start'

    ) select * from c

  • This can be done more efficiently with a simple ROW_NUMBER implementation.

    😎

    The problem with your solution is that it scans the table 1+(2 x number of rows) times, fear it will be very inefficient on larger sets.

    Single table scan solution

    ;with SAMPLE_DATA as (

    select 'start' [eventtype],cast('2016-05-17 08:00:00' as datetime) [eventdatetime]

    union select 'start',cast('2016-05-17 09:10:00' as datetime)

    union select 'start',cast('2016-05-17 09:40:00' as datetime)

    union select 'stop',cast('2016-05-17 09:50:00' as datetime)

    union select 'stop',cast('2016-05-17 09:12:00' as datetime)

    union select 'stop',cast('2016-05-17 08:10:00' as datetime)

    union select 'start',cast('2016-05-17 09:40:00' as datetime)

    union select 'stop',cast('2016-05-17 09:50:00' as datetime)

    union select 'stop',cast('2016-05-17 09:12:00' as datetime)

    union select 'start',cast('2016-05-17 10:00:00' as datetime)

    union select 'stop',cast('2016-05-17 10:50:00' as datetime)

    union select 'start',cast('2016-05-17 10:40:00' as datetime)

    union select 'stop',cast('2016-05-17 11:50:00' as datetime)

    )

    ,GROUPED_DATA AS

    (

    SELECT

    (ROW_NUMBER() OVER

    (

    ORDER BY SD.eventdatetime

    ) / 2) +

    CASE

    WHEN SD.eventtype = 'start' THEN 1

    ELSE 0

    END AS GRP

    ,SD.eventtype

    ,SD.eventdatetime

    FROM SAMPLE_DATA SD

    )

    SELECT

    MAX(CASE WHEN GB.eventtype = 'start' THEN GB.eventdatetime END) AS starttime

    ,MAX(CASE WHEN GB.eventtype = 'stop' THEN GB.eventdatetime END) AS endtime

    FROM GROUPED_DATA GB

    GROUP BY GB.GRP;

    Output

    starttime endtime

    ----------------------- -----------------------

    2016-05-17 08:00:00.000 2016-05-17 08:10:00.000

    2016-05-17 09:10:00.000 2016-05-17 09:12:00.000

    2016-05-17 09:40:00.000 2016-05-17 09:50:00.000

    2016-05-17 10:00:00.000 2016-05-17 10:50:00.000

    2016-05-17 10:40:00.000 2016-05-17 11:50:00.000

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply