SQL Server : pivot functionality, need to pivot a table

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, stopfrom(  select id2, status, time,    row_number() over(partition by status                      order by time) seq  from yourtable) dpivot(  max(time)  for status in (start, stop)) pivorder 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) stopfrom (  select id2, status, time,    row_number() over(partition by status                      order by time) seq  from yourtable) dgroup by id2, seq;

See SQL Fiddle with Demo

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 stopFROM MyTable AS mt1WHERE 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.