错误:列“DOB"的类型不正确与 UNPIVOT 列表中指定的其他列的类型冲突 [英] Error : The type of column "DOB" conflicts with the type of other columns specified in the UNPIVOT list

问题描述

我的查询，

简单地将行转换为列

SELECT
  ColumnName,
  value
FROM (SELECT
  id [ID],
  firstname [First Name],
  lastname [Last Name],
  dob [DOB],
  sex [Gender]
FROM client
WHERE id = '11') d
UNPIVOT
(
Value FOR
ColumnName IN ([ID], [First Name], [Last Name], [DOB], [Gender])
) unpiv;

但它显示一个错误.

DOB"列的类型与 UNPIVOT 列表中指定的其他列的类型冲突.

The type of column "DOB" conflicts with the type of other columns specified in the UNPIVOT list.

推荐答案

由于结果将带回行中的所有列，使用所有值构建一个新的派生列，您必须确保类型适合在一起.

As the result will bring back all columns in rows, building a new derived column with all the values, you must ensure, that the types fit together.

你可以用 CAST

SELECT
  ColumnName,
  value
FROM (SELECT
  CAST(id AS NVARCHAR(MAX)) [ID],
  CAST(firstname AS NVARCHAR(MAX)) [First Name],
  CAST(lastname AS NVARCHAR(MAX)) [Last Name],
  CAST(dob AS NVARCHAR(MAX)) [DOB],
  CAST(sex AS NVARCHAR(MAX)) [Gender]
FROM client
WHERE id = '11') d
UNPIVOT
(
Value FOR
ColumnName IN ([ID], [First Name], [Last Name], [DOB], [Gender])
) unpiv;

DOB 将转换为您机器的默认设置.使用 CONVERT 您可以强制执行给定的日期/时间格式.

The DOB will be converted to the default setting of your machine. Using CONVERT you might enforce a given date/time format.

这篇关于错误:列“DOB"的类型不正确与 UNPIVOT 列表中指定的其他列的类型冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！