PIVOT statement error

Question

I cannot get working PIVOT over following data set

Machine   AtTime                     ValueString
-------------------------------------------------
3     2017-06-01 11:42:33.000         Start

3     2017-06-01 11:43:46.000         Stop

5     2017-06-01 12:42:33.000         Start

5     2017-06-01 12:43:46.000         Stop

SQL

SELECT Machine, TimeStart, TimeEnd
FROM
(
      SELECT  Machine, [AtTime], [ValueString]
  FROM [dbo].[DATA_ACTION]
)
) d
PIVOT
(
  MAX(AtTime)
  FOR [AtTime] in (TimeStart, TimeEnd)
) piv;

Error

There is an error during converting data type nvarchar to datetime.

Objective

Get table like this

Machine  TimeStart                  TimeEnd
------------------------------------------------------------
3        2017-06-01 11:42:33.000    2017-06-01 11:43:46.000
5        2017-06-01 12:42:33.000    2017-06-01 12:43:46.000

Answer 1

You should use Valuestring:

create table data_action(Machine  nvarchar(50), AtTime datetime,ValueString nvarchar(50))
insert into data_action values (3,'2017-06-01 11:42:33.000','Start')
insert into data_action values (3,'2017-06-01 11:43:46.000','Stop')
insert into data_action values (5,'2017-06-01 12:42:33.000','Start')
insert into data_action values (5,'2017-06-01 12:43:46.000','Stop')


SELECT machine,start as [TimeStart],stop as [TimeEnd]
FROM
(
 SELECT  Machine, [AtTime], [ValueString]
  FROM [dbo].[DATA_ACTION]
) d
PIVOT
(
  MAX(AtTime)
  FOR [ValueString] in ([Start] , [Stop])
) piv;

The result is:

*---------*-------------------------*------------------------*   
|machine  |TimeStart                |TimeEnd                 | 
*---------*-------------------------*------------------------*      
|3        |2017-06-01 11:42:33.000  |2017-06-01 11:43:46.000 | 
*---------*-------------------------*------------------------*      
|5        |2017-06-01 12:42:33.000  |2017-06-01 12:43:46.000 |
*---------*-------------------------*------------------------*