创建SQL透视表时遇varchar转bigint类型转换错误求解决

错误原因 #

你当前的代码在子查询中将秒数总和直接格式化为字符串，然后在PIVOT中对这个字符串执行MAX聚合操作。由于原表的ride_length_in_sec是数值类型，SQL Server会尝试将拼接后的字符串隐式转换为数值类型以完成聚合，从而触发Error converting data type varchar to bigint错误。

解决方案 #

先在子查询中计算出数值型的总秒数，完成透视表操作后，再对结果进行格式化拼接：

SELECT 
    'Total hours ridden' AS Metric,
    -- 格式化Member的总秒数
    CONCAT(
        [Member]/3600, ' hours, ',
        ([Member]%3600)/60, ' minutes, ',
        ([Member]%3600)%60, ' seconds'
    ) AS [Member],
    -- 格式化Casual的总秒数
    CONCAT(
        [Casual]/3600, ' hours, ',
        ([Casual]%3600)/60, ' minutes, ',
        ([Casual]%3600)%60, ' seconds'
    ) AS [Casual]
FROM
    (SELECT 
         member_casual, 
         SUM(CAST(ride_length_in_sec AS BIGINT)) AS total_seconds
     FROM
         AllCombined
     GROUP BY 
         member_casual) AS Src2
PIVOT
    (MAX(total_seconds)    
        FOR member_casual 
            IN ([Member], [Casual])
    ) AS Pvt2

代码说明 #

• 子查询Src2：仅计算每个用户类型的总秒数，保持数值类型（BIGINT），避免提前格式化导致类型冲突。
• PIVOT操作：对数值型的total_seconds执行MAX聚合，生成透视表结构，此时[Member]和[Casual]列仍为数值类型。
• 外层查询：将透视表中的数值型秒数格式化为XX hours, XX minutes, XX seconds的字符串格式，完成最终展示。

补充说明 #

你之前尝试的修改（改列类型、用+拼接）无效，核心原因是PIVOT阶段需要处理数值型数据，提前将其转为字符串会破坏聚合操作的类型一致性。将格式化步骤移到透视表之后，就能彻底避免类型转换错误。

内容的提问来源于stack exchange，提问作者Ahsan Ayub