主页 / dba / 问题 / 135587
Accepted
ΩmegaMan
Asked: 2016-04-18 13:37:43 +0800 CST

添加日期时间时交叉应用失败

  • 772

在尝试实现将Pivot两行数据表变成一行的目标时，我的第一个想法是使用Cross Apply. 通过使用Cross Apply可以识别/生成具有从行的唯一 ID 派生的特定列名的每一行。（例如，“纬度”源列的“Lat1”和“Lat2”）。

INT当我使用or数据类型时，一切都很好，FLOAT但是当我尝试使用DateTimeall 时，一切都VALUES变成了DateTime。

航运纬度/经度/日期时间示例

在两个不同的日子里，我们在船上的旅程中有两个要点：

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');

成功交叉应用 如果我们使用CrossApply前三列的数据

  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)

我们的结果符合预期：

Int 和 Float 交叉应用成功

伟大的！

与 DateTime 交叉应用失败

但是一旦我们添加DateTime到组合中：

      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)

都变成DateTimes

DateTime 交叉应用失败

如果我的最终目标是将原始表中的所有值转换为一行，应该如何解决这个问题？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    结果，值列必须像往常一样具有数据类型。SQL Server 使用数据类型优先级规则确定类型（更准确地说，VALUES子句是 a UNION，所以类型在那里匹配）。

    在您的第一个示例中，优先规则给出了float类型的列。在第二个示例中，它是datetime。

    通过将子句中的每个值VALUES显式转换为通用类型（例如varchar或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);

    或者：

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

    注意：并非所有内容都可以转换为sql_variant。从那个链接：

    限制

    除了旧的已弃用图像类型之外的所有内容都可以转换为char或varchar。请参阅数据类型转换（数据库引擎）中的转换矩阵：

    转换矩阵

    • 15

相关问题