transform sql rows to column

Question

I have a very unusual request. I have some filtered data in a table, columns being ID, Date, and Event. Event is an XML column where in one of the tag is StartWork and EndWork. From huge data I have filtered out data for a request. My Sample data has 6 rows, in sequence of StartWork, EndWork, StartWork, EndWork and so on. What exactly I want to do is find time difference between each combination. I mean, EndWork - StartWork = Difference. Next EndWork - StartWork = Difference2 and so on.

Basically I want, 2-1, 4-3, 6-5 and so on. I tried doing it with Pivot, but couldn't get desired result

Hercule · Accepted Answer · 2015-08-20 10:52:03Z

2

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.LineNb

answered Aug 20, 2015 at 10:52

Hercule

1861 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gottfried Lesigang · Accepted Answer · 2015-08-20 11:03:02Z

1

This should help you:

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

edited Aug 20, 2015 at 11:03

answered Aug 20, 2015 at 10:43

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

anshul Over a year ago

the solution by @hercule is on the same logic, it just is a bit simpler. it doesn't use CTE.

Hisham Maudarbocus · Accepted Answer · 2015-08-20 11:04:04Z

0

Suppose "tbl" is your table name. Here you go:

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

Please flag it as answered if it works...

answered Aug 20, 2015 at 11:04

Hisham Maudarbocus

6208 silver badges19 bronze badges

Collectives™ on Stack Overflow

transform sql rows to column

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related