SQL Server:数据透视功能，需要数据透视表 [英] SQL Server : pivot functionality, need to pivot a table

问题描述

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

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

是否可以这样做?我在SQL Server中尝试了数据透视，但是它仅返回一条记录.有人可以帮忙吗?

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

推荐答案

您可以使用PIVOT函数获取结果，我只需将row_number()窗口函数应用于数据，以便为每个ID2:

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;

请参见带演示的SQL提琴.

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

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;

请参见带演示的SQL小提琴

这篇关于SQL Server:数据透视功能，需要数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！