多个记录合并成一条线

问题描述：

我有一个SQL Server 2005的表记录了过程的每个步骤如下图所示多个记录合并成一条线

ResourceID EventType   Time 
ABC123  Job Activated  2013-01-08 10:01:31.000 
ABC123  Download Complete 2013-01-08 10:03:32.000 
ABC123  Job Complete  2013-01-08 10:07:42.000 
XYZ789  Job Activated  2013-01-08 12:05:11.000 
XYZ789  Download Complete 2013-01-08 12:08:52.000 
XYZ789  Job Complete  2013-01-08 12:14:21.000

我想什么做的是显示对每一个单行结果ResourceID显示每个事件的时间。

ResourceID Job Activated    Download Complete  Job Complete 
ABC123  2013-01-08 10:01:31.000 2013-01-08 10:03:32.000 2013-01-08 10:07:42.000 
XYZ789  2013-01-08 12:05:11.000 2013-01-08 12:08:52.000 2013-01-08 12:14:21.000

有关如何完成此任务的任何建议？谢谢。

您想使用PIVOT。我是手机，所以我不能为你写。 – rbedger

查看'PIVOT'函数：http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx –

答

由于意见建议，你可以用PIVOT做到这一点很容易：

DECLARE @x TABLE(ResourceID CHAR(6), EventType VARCHAR(32), [Time] DATETIME); 

INSERT @x SELECT 'ABC123','Job Activated ','2013-01-08 10:01:31.000' 
UNION ALL SELECT 'ABC123','Download Complete','2013-01-08 10:03:32.000' 
UNION ALL SELECT 'ABC123','Job Complete  ','2013-01-08 10:07:42.000' 
UNION ALL SELECT 'XYZ789','Job Activated ','2013-01-08 12:05:11.000' 
UNION ALL SELECT 'XYZ789','Download Complete','2013-01-08 12:08:52.000' 
UNION ALL SELECT 'XYZ789','Job Complete  ','2013-01-08 12:14:21.000'; 

SELECT ResourceID,[Job Activated],[Download Complete],[Job Complete] 
FROM @x 
PIVOT 
(
    MAX([Time]) FOR EventType IN (
    [Job Activated],[Download Complete],[Job Complete] 
)) AS p;

我只是在回答我的问题，因为偷了我的支点 – Taryn

@bluefeet嘿，你不能拥有'全部'''枢轴''。我会把困难的事情留给你。 :-) –

哦，我仍然会回答，只是有其他选择。 :) – Taryn

答

可以使用聚合函数与CASE表达式来透视数据：

select resourceid, 
    max(case when EventType = 'Job Activated' then [time] end) as [Job Activated], 
    max(case when EventType = 'Download Complete' then [time] end) as [Download Complete], 
    max(case when EventType = 'Job Complete' then [time] end) as [Job Complete] 
from yourtable 
group by resourceid

见SQL Fiddle with Demo。

或者您可以使用多个连接上你的餐桌：

select ja.resourceid, 
    ja.[time] as [Job Activated], 
    dc.[time] as [Download Complete], 
    jc.[time] as [Job Complete] 
from yourtable ja 
left join yourtable dc 
    on ja.resourceid = dc.resourceid 
    and dc.EventType = 'Download Complete' 
left join yourtable jc 
    on ja.resourceid = jc.resourceid 
    and jc.EventType = 'Job Complete' 
where ja.EventType = 'Job Activated'

见SQL Fiddle with Demo

多个记录合并成一条线

相关推荐