SQL使用PIVOT行转列时数据类型转换与运算符报错问题求助

问题原因 #

• PIVOT运算符IN子句的取值与源数据行号类型不匹配：子查询中通过ROW_NUMBER生成的line_no是1/2/3/4的数值类型，但是PIVOT的IN子句中填写的是[doc1]这类字符串值，二者类型不匹配触发类型转换报错（Msg 8114），同时值与line_no实际取值不匹配，触发PIVOT值错误（Msg 473）。
• 额外排查项：你提供的Table_2表结构中不存在index字段，若实际业务中无该字段，需要删除WHERE子句中b.index = {value}的过滤条件。

正确实现方案 #

提供两种可达到预期输出的写法，按需选择即可：

方案1：PIVOT后对列重命名 #

SELECT 
    res_id,
    [1] AS doc1,
    [2] AS doc2,
    [3] AS doc3,
    [4] AS doc4
FROM (
    SELECT 
        a.res_id, 
        b.title, 
        ROW_NUMBER() OVER (PARTITION BY a.res_id ORDER BY b.title) AS line_no
    FROM table_2 b
    INNER JOIN table_1 a ON a.id = b.id
    -- 若实际业务存在index字段则保留该过滤条件，否则删除
    -- WHERE b.index = {value}
) AS p
PIVOT (
    MAX(title)
    FOR line_no IN ([1], [2], [3], [4])
) AS pvt

方案2：子查询中提前生成列名字符串 #

SELECT res_id, doc1, doc2, doc3, doc4
FROM (
    SELECT 
        a.res_id, 
        b.title, 
        'doc' + CAST(ROW_NUMBER() OVER (PARTITION BY a.res_id ORDER BY b.title) AS VARCHAR(10)) AS line_no
    FROM table_2 b
    INNER JOIN table_1 a ON a.id = b.id
    -- 若实际业务存在index字段则保留该过滤条件，否则删除
    -- WHERE b.index = {value}
) AS p
PIVOT (
    MAX(title)
    FOR line_no IN ([doc1], [doc2], [doc3], [doc4])
) AS pvt

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