7

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:

Cross Apply success with Int and Float

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

Cross Apply failure with DateTime

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

14

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:

Restrictions

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

Conversion matrix

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.