SQL Server : pivot functionality, need to pivot a table

correlated-subquery, pivot, sql, sql-server, subquery

Solution

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

Problem

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?

Original source