Cross Apply Fails When DateTime Added

Question

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?

Answer 1

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):