对不同服务器/数据库中的表进行反透视失败，并出现"与其他列的类型冲突"错误

Question

我正试图在另一个数据库的表上从存储过程中运行UNPIVOT语句。我可以在本地运行UNPIVOTfine，但当我在不同的数据库/服务器上运行它时，我会收到以下错误。

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

这是我的UNPIVOT的精简版。

CREATE TABLE temp (
id nvarchar(100),
F1 nvarchar(100),
F2 nvarchar(100),
F3 decimal(5,0)
)
INSERT INTO temp VALUES(N'100', N'111', N'AAA', 123)
INSERT INTO temp VALUES(N'100', N'222', N'BBB', 456)
INSERT INTO temp VALUES(N'100', N'333', N'CCC', 789) 
INSERT INTO temp VALUES(N'100', N'444', N'DDD', 012)
INSERT INTO temp VALUES(N'100', N'555', N'EEE', 345)
SELECT * FROM (   SELECT
ID,
CAST(F1 AS NVARCHAR(MAX)) AS F1,--used in order to normalize the data
CAST(F2 AS NVARCHAR(MAX)) AS F2,--used in order to normalize the data
CAST(F3 AS NVARCHAR(MAX)) AS F3--used in order to normalize the data
FROM
TEMP   ) AS T UNPIVOT(FieldValue for FieldName in (   F1, F2, F3   )) AS UNPVT

SQL Fiddle

我确实注意到，当表I为UNPIVOTing，具有DECIMAL()字段类型时，会出现此错误。

和往常一样，任何帮助都会受到赞赏。

---

更新：似乎在远程服务器上创建视图将允许CAST正常发生。但为什么呢？我也尝试过使用cte，但没有成功。我想避免创建视图。希望这不是唯一的解决方案。

Answer 1

错误

列的类型"XXX"；与UNPIVOT列表中指定的其他列的类型冲突。

是非常明显的，也是更好的消息之一。它告诉您的UNPIVOT列表包含不同类型的列。以下是导致此类错误的示例：SQL Fiddle

SELECT * FROM (
SELECT
ID,
CAST(F1 AS NVARCHAR(MAX)) AS F1,
CAST(F2 AS NVARCHAR(MAX)) AS F2,
F3  -- note F3 is a decimal column containing decimal values
FROM
TEMP
) AS T
UNPIVOT(FieldValue for FieldName in (
F1, F2, F3  -- F3 type is different from F1/F2
)) AS UNPVT

在本地查询中，如果您的查询是在问题SQL Fiddle中写的，则它不可能引发此错误，因为您已经将所有列CAST为NVARCHAR(MAX)。

但是在远程查询中，SQL Server似乎在编译时使查询失败。它似乎在CAST之前预先验证了数据透视列——似乎是因为可能在本地和执行流中的不同点执行CAST。解决方法似乎是创建一个VIEW，以解决远程服务器上的CAST。

变通办法

您可以使用FOR XML强制查询进行远程解析(至少在2005年作为远程服务器进行了测试)，这样做的好处是，为了一致性，无论如何都会使用nvarchar(max)提取回值。

SELECT *
FROM (
SELECT n.value('ID[1]','nvarchar(max)') ID
,n.value('F1[1]','nvarchar(max)') F1
,n.value('F2[1]','nvarchar(max)') F2
,n.value('F3[1]','nvarchar(max)') F3
FROM (   
SELECT (
SELECT
ID,
F1,
F2,
F3
FROM
[akltrsvrdbsqllatin].test1.dbo.temp
FOR XML PATH('a'), type
) XmlPacket
) x
cross apply XmlPacket.nodes('/a') n(n)
) T UNPIVOT(FieldValue for FieldName in (   F1, F2, F3   )) AS UNPVT

---

SQLKiwi发现，使用sql_variant作为CAST目标可以实现远程查询，如这里的聊天记录所示。

SELECT
*
FROM
(
SELECT
[id],
CONVERT(sql_variant, [F1]) as F1,
CONVERT(sql_variant, [F2]) as F2,
CONVERT(sql_variant, [F3]) as F3,
CONVERT(sql_variant, CAST([D20] as nvarchar(4000))) as F20--in case the source field is text (Make sure that 4000 is enough characters!)
FROM
[server].[database].[scheme].[table]
) as t
UNPIVOT(FieldValue for FieldName in ([F1],[F2],[F3],[D20])) as unpvt