使用CROSS APPLY逆透视含INT与VARCHAR列时遇转换错误求助

问题原因 #

原代码中，CROSS APPLY的VALUES子句同时包含int类型列（View0/View1/View2）和varchar类型的Remarks列。SQL Server会自动推断column_value列的最高优先级数据类型为int（int优先级高于varchar），因此会尝试将Remarks的varchar值转换为int，触发如下错误：

Conversion failed when converting the varchar value 'View Test' to data type int.

解决方案 #

将int类型列和varchar类型列分开处理，通过UNION ALL合并结果。此方法无需显式使用CAST或CONVERT进行类型转换：

DECLARE @tableData AS TABLE 
(
    TD_ID INT PRIMARY KEY IDENTITY(1,1),
    View0 INT, 
    View1 INT,
    View2 INT,
    Remarks Varchar(20)
)

INSERT INTO @tableData (View0, View1, View2, Remarks)
VALUES(1, 0, 1, 'View Test')

SELECT column_Name, column_value
FROM @tableData
CROSS APPLY (
    VALUES
    ('View0', View0),
    ('View1', View1),
    ('View2', View2)
) Q (column_Name, column_value)
UNION ALL
SELECT column_Name, column_value
FROM @tableData
CROSS APPLY (
    VALUES
    ('Remarks', Remarks)
) Q (column_Name, column_value)

原理说明 #

分开处理后，第一个CROSS APPLY的column_value为int类型，第二个为varchar类型。执行UNION ALL时，SQL Server会自动将int类型隐式转换为varchar类型（兼容两种数据类型的最低优先级类型），既避免了强制转换varchar到int的错误，也无需手动编写转换函数。

内容的提问来源于stack exchange，提问作者Deep Thumar