Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a specific column name derived from the row's unique id. (e.g. 'Lat1' and 'Lat2 for a 'Latitude' source column).

All is fine when I use INT or FLOAT data types but when I try to use a DateTime all VALUES become DateTime.

Example Shipping Lat/Long/DateTime

We have two points in a ships journey reported on two different days:

CREATE TABLE #Shipping
(
     [RouteID]     [INT]  NOT NULL,
     [Latitude]    FLOAT NOT NULL, 
     [Longitude]   FLOAT NOT NULL,
     [Time]        DATETIME NOT NULL

);

INSERT #Shipping(RouteID, [Latitude], [Longitude], [Time])
VALUES          (1,         18.0221,    -63.1206,  '24-Jan-2016'), 
                (2,         17.8353,    -62.99667, '25-Jan-2016');

Successful CrossApply
If we use CrossApply against the data for the first three columns

SELECT  col+cast([RouteID] as varchar(1)) new_col
         , X.value
       FROM #Shipping
  CROSS APPLY
  (
      VALUES
         (RouteID,   'Id')
      ,  (Latitude,  'Lat')
      ,  (Longitude, 'Lon')
  ) X (value, col)

Our results are as expected:

Great!

Failure CrossApply With DateTime

But once we add DateTime to the mix:

SELECT  col+cast([RouteID] as varchar(1)) new_col
             , X.value
           FROM #Shipping
      CROSS APPLY
      (
          VALUES
             (RouteID,   'Id')
          ,  (Latitude,  'Lat')
          ,  (Longitude, 'Lon')
          , ([Time],    'Time')
      ) X (value, col)

All become DateTimes

How should one work around this if my end goal is to pivot all the values from the original table into one row?

In the result, the value column must have a data type, like always. SQL Server determines the type using the rules for data type precedence (more precisely, the VALUES clause is a UNION, so types are matched there).

In your first example, the precedence rules give a column of type float. In the second example, it is datetime.

Solve the problem by explicitly casting each value in the VALUES clause explicitly to a common type, e.g. varchar or sql_variant:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)),
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(sql_variant, S.RouteID),   'Id'),
        (CONVERT(sql_variant, S.Latitude),  'Lat'),
        (CONVERT(sql_variant, S.Longitude), 'Lon'),
        (CONVERT(sql_variant, S.[Time]), 'Time')
) AS X (value, col);

or:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)), 
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(varchar(8), S.RouteID),   'Id'),
        (CONVERT(varchar(8), S.Latitude),  'Lat'),
        (CONVERT(varchar(8), S.Longitude), 'Lon'),
        (CONVERT(varchar(8), S.[Time], 112), 'Time')
) AS X (value, col);

Note: Not everything can be cast to sql_variant. From that link:

Everything except the old deprecated image type can be cast to char or varchar. See the conversion matrix in Data Type Conversion (Database Engine):

StackExchange Database Administrators Q#135587, answer score: 15