Início / dba / Perguntas / 135587
Accepted
ΩmegaMan
Asked: 2016-04-18 13:37:43 +0800 CST

Falha na aplicação cruzada quando DateTime adicionado

  • 772

Ao tentar atingir um Pivotobjetivo -ed para uma tabela de dados de duas linhas em uma linha, meu primeiro pensamento foi usar um arquivo Cross Apply. Usando o Cross Applypode-se identificar/gerar cada uma das linhas com um nome de coluna específico derivado do id exclusivo da linha. (por exemplo, 'Lat1' e 'Lat2 para uma coluna de origem 'Latitude').

Tudo está bem quando eu uso ou INTtipos FLOATde dados, mas quando tento usar um DateTimearquivo .VALUESDateTime

Exemplo Lat/Long/DateTime de Envio

Temos dois pontos em uma viagem de navios relatados em dois dias diferentes:

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 bem-sucedido Se usarmos CrossApplyos dados das três primeiras colunas

  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)

Nossos resultados são os esperados:

Cross Apply sucesso com Int e Float

Excelente!

Falha CrossApply com DateTime

Mas uma vez que adicionamos DateTimeà mistura:

      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)

todos se tornam DateTimes

Falha de aplicação cruzada com DateTime

Como contornar isso se meu objetivo final é dinamizar todos os valores da tabela original em uma linha?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    No resultado, a coluna de valor deve ter um tipo de dado, como sempre. O SQL Server determina o tipo usando as regras de precedência de tipo de dados (mais precisamente, a VALUEScláusula é uma UNION, portanto, os tipos são correspondidos lá).

    Em seu primeiro exemplo, as regras de precedência fornecem uma coluna do tipo float . No segundo exemplo, é datetime .

    Resolva o problema convertendo explicitamente cada valor na VALUEScláusula explicitamente para um tipo comum, por exemplo, varchar ou 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);

    ou:

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

    Observação: nem tudo pode ser convertido em sql_variant . A partir desse link:

    Restrições

    Tudo, exceto o antigo tipo obsoleto de imagem , pode ser convertido em char ou varchar . Veja a matriz de conversão em Data Type Conversion (Database Engine) :

    Matriz de conversão

    • 15

relate perguntas