SQL Server : pivot functionality, need to pivot a table

Question

I have data in SQL Server in the format below.

-ID          ID2  status  time
-1384904453  417  stop    2013-11-19 23:40:43.000
-1384900211  417  start   2013-11-19 22:30:06.000
-1384822614  417  stop    2013-11-19 00:56:36.000
-1384813810  417  start   2013-11-18 22:30:06.000
-1384561199  417  stop    2013-11-16 00:19:45.000
-1384554623  417  start   2013-11-15 22:30:06.000
-1384475231  417  stop    2013-11-15 00:26:58.000
-1384468224  417  start   2013-11-14 22:30:06.000
-1384388181  417  stop    2013-11-14 00:16:20.000
-1384381807  417  start   2013-11-13 22:30:06.000
-1384300222  417  stop    2013-11-12 23:50:11.000
-1384295414  417  start   2013-11-12 22:30:06.000
-1384218209  417  stop    2013-11-12 01:03:17.000
-1384209015  417  start   2013-11-11 22:30:06.000

What I need is to be able to show data in the following format.

-ID2  start                      stop
-417  2013-11-19 22:30:06.000    2013-11-19 23:40:43.000
-417  2013-11-18 22:30:06.000    2013-11-19 00:56:36.000

Is it possible to do this? I tried pivot in SQL Server but it only returns one record. Can someone help please?

Answer 1

You can use the PIVOT function to get the result, I would just apply the row_number() windowing function to the data so you can return multiple rows for each ID2:

select id2, start, stop
from
(
  select id2, status, time,
    row_number() over(partition by status
                      order by time) seq
  from yourtable
) d
pivot
(
  max(time)
  for status in (start, stop)
) piv
order by start desc;

See SQL Fiddle with Demo.

You could also use an aggregate function with a CASE expression to get the final result:

select
  id2,
  max(case when status = 'start' then time end) start,
  max(case when status = 'start' then time end) stop
from 
(
  select id2, status, time,
    row_number() over(partition by status
                      order by time) seq
  from yourtable
) d
group by id2, seq;

See SQL Fiddle with Demo

Answer 2

You do not need a PIVOT query to get the information you are needing. You can perform the following:

SELECT mt1.ID2, mt1.time AS start,
(
   SELECT TOP 1 mt2.time
   FROM MyTable AS mt2
   WHERE mt2.status = 'stop' 
     AND mt2.time >= mt1.time
   ORDER BY mt2.time 
) AS stop
FROM MyTable AS mt1
WHERE mt1.status = 'start' 

If you are performing the above query in SQL Server and not MS Access then you will need to use TOP(1) instead of just TOP 1.

Here is the SQL Fiddle demonstrating the above query in SQL Server.