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?

Paul White · Accepted Answer · 2016-04-18 03:23:52Z

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

Stack Exchange Network

Cross Apply Fails When DateTime Added

1 Answer 1

Hot Network Questions

Cross Apply Fails When DateTime Added

1 Answer 1

Related

Hot Network Questions