SQL Server中UNPIVOT与SELECT语句结合报错问题求助

错误原因 #

你遇到的Subquery returned more than 1 value错误，核心原因是SELECT子句里嵌套的子查询是无关联的独立查询，它会返回UNPIVOT后的全部10行结果，但SQL Server不允许将多行结果直接作为单个列的取值——当子查询用作表达式时，必须保证只返回单行值。

解决方案 #

要实现“第一行显示book_veh_num，其余行该列为NULL，同时对应所有UNPIVOT数据”的效果，需要用CROSS APPLY关联主查询与UNPIVOT的结果，再结合窗口函数ROW_NUMBER()标记行号，控制book_veh_num的显示逻辑：

SELECT 
    CASE WHEN rn = 1 THEN v.book_veh_num ELSE NULL END AS 列A,
    unpiv.description AS 列B
FROM 
    dba.pp_Datasource_IL1201_Auto_Vehicles(8589100, 'BusAuto') v
CROSS APPLY (
    SELECT 
        description,
        ROW_NUMBER() OVER (ORDER BY 
            CASE vehicle 
                WHEN 'veh_status' THEN 1
                WHEN 'veh_num' THEN 2
                WHEN 'veh_year' THEN 3
                WHEN 'veh_make' THEN 4
                WHEN 'veh_model' THEN 5
                WHEN 'veh_vin' THEN 6
                WHEN 'veh_cost_new' THEN 7
                WHEN 'veh_garage_loc' THEN 8
                WHEN 'veh_class_code' THEN 9
                WHEN 'blank_line' THEN 10
            END) AS rn
    FROM (
        SELECT
            CONVERT(VARCHAR(255), veh_status) AS veh_status,
            CONVERT(VARCHAR(255), veh_num) AS veh_num,
            CONVERT(VARCHAR(255), veh_year) AS veh_year,
            CONVERT(VARCHAR(255), veh_make) AS veh_make,
            CONVERT(VARCHAR(255), veh_model) AS veh_model,
            CONVERT(VARCHAR(255), veh_vin) AS veh_vin,
            CONVERT(VARCHAR(255), veh_cost_new) AS veh_cost_new,
            CONVERT(VARCHAR(255), veh_garage_loc) AS veh_garage_loc,
            CONVERT(VARCHAR(255), veh_class_code) AS veh_class_code,
            CONVERT(VARCHAR(255), ' ') AS blank_line
        FROM dba.pp_Datasource_IL1201_Auto_Vehicles(8589100, 'BusAuto')
        WHERE book_veh_num = v.book_veh_num -- 关联主查询的车辆编号，确保数据对应
    ) d
    UNPIVOT (
        description FOR vehicle IN (
            veh_status, veh_num, veh_year, veh_make, veh_model,
            veh_vin, veh_cost_new, veh_garage_loc, veh_class_code, blank_line
        )
    ) unpiv
) ca
ORDER BY rn;

代码说明 #

1. CROSS APPLY：将主查询的每一行车辆数据与对应的UNPIVOT结果关联，确保每个车辆的10条UNPIVOT数据都能匹配到对应的主行。
2. ROW_NUMBER() + CASE：给每个车辆的UNPIVOT数据按指定列顺序编号，只在第一行（rn=1）显示book_veh_num，其余行设为NULL。
3. 关联条件：子查询里的WHERE book_veh_num = v.book_veh_num保证UNPIVOT的数据只对应当前主查询行的车辆，避免跨车辆的数据混乱。

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