将sql行转换为列
将sql行转换为列
提问于 2015-08-20 10:38:39
我有一个很不寻常的要求。表中有一些筛选过的数据，列为ID、日期和事件。事件是一个XML列，其中一个标记是StartWork和EndWork。从巨大的数据中，我过滤掉了请求的数据。我的样本数据有6行，按StartWork、EndWork、StartWork、EndWork等顺序排列。我真正想做的是找出每个组合之间的时间差。我是说，EndWork - StartWork =差异。接下来是EndWork - StartWork = Difference2，等等。
基本上我想要，2-1，4-3，6-5等等。我试着用Pivot来做，但没有得到想要的结果
回答 3
Stack Overflow用户
回答已采纳
发布于 2015-08-20 10:52:03
Select DateDiff(minute,StartWork.datacreated,EndWork.datacreated)
from
(Select datacreated,LineNb=row_number() over(Order by datacreated) from Table where eventdata.value('(/data/status/text())[1]','varchar(15)')='StartWork') StartWork
INNER JOIN
(Select datacreated,LineNb=row_number() over(Order by datacreated) from Table where eventdata.value('(/data/status/text())[1]','varchar(15)')='EndWork') EndWork
ON StartWork.LineNb=EndWork.LineNbStack Overflow用户
发布于 2015-08-20 10:43:35
这应该对你有帮助：
declare @xmlStart XML='<data><status>StartWork</status></data>';
declare @xmlENd XML='<data><status>EndWork</status></data>';
declare @tbl TABLE(id INT,datecreated DATETIME,eventdata XML);
INSERT INTO @tbl VALUES(1,{ts'2015-07-29 09:17:34'},@xmlStart)
,(2,{ts'2015-07-29 09:20:24'},@xmlEnd)
,(3,{ts'2015-07-29 10:05:41'},@xmlStart)
,(4,{ts'2015-07-29 10:18:34'},@xmlEnd);
WITH resolvedCTE AS
(
SELECT TOP 100 PERCENT id,datecreated,eventdata.value('(/data/status)[1]','varchar(max)') AS EventStatus FROM @tbl
)
,StartEvnets AS
(
SELECT ROW_NUMBER() OVER(ORDER BY datecreated) AS inx,id,datecreated FROM resolvedCTE WHERE EventStatus='StartWork'
)
,EndEvnets AS
(
SELECT ROW_NUMBER() OVER(ORDER BY datecreated) AS inx,id,datecreated FROM resolvedCTE WHERE EventStatus='EndWork'
)SELECT StartEvnets.id, CAST(EndEvnets.datecreated - StartEvnets .datecreated AS TIME)
FROM StartEvnets
INNER JOIN EndEvnets ON StartEvnets.inx =EndEvnets.inx Stack Overflow用户
发布于 2015-08-20 11:04:04
假设"tbl“是您的表名。给你：
SELECT t.datecreated - t2.datecreated
FROM tbl t, tbl t2
WHERE t.id IN
(SELECT id FROM tbl WHERE _eventdata LIKE ('%EndWork%'))
AND t2.id = t.id -1如果它起作用的话，请按回答来标记它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32116007
复制相关文章
相似问题
腾讯云开发者
