CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Greg
Greg

Reputation: 4055

TSQL: coverting rows to columns

We are tracking time and need to report time spent for a given shift. Table looks like the below, and repeats for service orders, but with varying stageids.

serviceorderid stageid  recid       datetimeexecuted
WO-439660   Travelling  5639344479  2016-08-22 19:30:00.000
WO-439660   Started     5639344574  2016-08-22 20:30:00.000
WO-439660   Complete    5639345177  2016-08-22 22:30:00.000
WO-439660   Travelling  5639345178  2016-08-22 22:30:00.000
WO-439660   Suspended   5639349633  2016-08-22 23:00:00.000
WO-439660   Travelling  5639349917  2016-08-24 21:00:00.000
WO-439660   Started     5639349918  2016-08-24 21:00:00.000
WO-439660   Suspended   5639349920  2016-08-24 21:45:00.000
WO-439660   Travelling  5639349921  2016-08-24 21:45:00.000
WO-439660   Started     5639349923  2016-08-24 22:15:00.000
WO-439660   Complete    5639349925  2016-08-24 22:45:00.000
WO-439660   Travelling  5639349926  2016-08-24 22:45:00.000
WO-439660   Started     5639349927  2016-08-24 23:30:00.000
WO-439660   Complete    5639349928  2016-08-24 23:30:00.000

I need to pivot it so that I have one row per work order that starts with staged = Traveling. like this:

serviceorderid   travel      started     completed   susp
WO-439660        5639344479  5639344574  5639345177  
WO-439660        5639345178                          5639349633
WO-439660        5639349917  5639349918              5639349920
WO-439660        5639349921  5639349923  5639349925

etc.

This has to be written in a view.

A shift always starts with stageId = Travelling, if I can group everything from one Travelling to another, then I should be able to pivot. I cannot group by date since a shift can cross days. Struggling with the TSQL here, any help appreciated.

Upvotes: 0

Views: 49

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Something like this should work:

;WITH CTE AS (
   SELECT serviceorderid, stageid, recid, datetimeexecuted,
          COUNT(IIF(stageid='Travelling', 1, NULL)) 
          OVER 
          (PARTITION BY serviceorderid 
           ORDER BY datetimeexecuted, 
                    IIF(stageid = 'Started', 3,
                        IIF(stageid = 'Travelling', 2, 1))) AS grp
   FROM mytable
)
SELECT serviceorderid,
       MAX(CASE WHEN stageid='Travelling' THEN datetimeexecuted END) AS travel,
       MAX(CASE WHEN stageid='Started' THEN datetimeexecuted END) AS started,
       MAX(CASE WHEN stageid='Complete' THEN datetimeexecuted END) AS completed,
       MAX(CASE WHEN stageid='Suspended' THEN datetimeexecuted END) AS susp
FROM CTE
GROUP BY serviceorderid, grp

Upvotes: 3

Related Questions