我在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
我需要的是能够以下列格式显示数据.
-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中尝试过pivot,但它只返回一条记录.有人可以帮忙吗？
解决方法
你可以使用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;
select
id2,max(case when status = 'start' then time end) start,max(case when status = 'start' then time end) stop
from
(
select id2,row_number() over(partition by status
order by time) seq
from yourtable
) d
group by id2,seq;
