SQL Server：pivot功能，需要透视表

I have data in SQL Server in the format below.

我在SQL Server中有以下格式的数据。

-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?

是否有可能做到这一点？我在SQL Server中尝试过pivot，但它只返回一条记录。有人可以帮忙吗？

2 个解决方案

#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:

你可以使用PIVOT函数来获得结果，我只是将row_number（）窗口函数应用于数据，这样你就可以为每个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.

请参阅SQL Fiddle with Demo。

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

您还可以使用带有CASE表达式的聚合函数来获取最终结果：

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

请参阅SQL Fiddle with Demo

#2

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

您不需要PIVOT查询来获取所需的信息。您可以执行以下操作：

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.

如果您在SQL Server而不是MS Access中执行上述查询，则需要使用TOP（1）而不是TOP 1。

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

这是SQL Fiddle在SQL Server中演示上述查询。

#1