含CASE语句的Pivot Table问题：如何正确获取最后一个运输商

你原来的SQL报错是因为聚合函数嵌套——SQL不允许在MAX()这类聚合函数的参数里再嵌套另一个MAX()，也就是MAX(Case ... When max(transporterlinenumber) ...)这种写法是非法的。

要实现获取第一个、第2-9个以及最后一个运输商的需求，可以用窗口函数先提前计算每个运单对应的最大运输商行号，再进行透视：

WITH TransporterWithMaxLine AS (
    SELECT 
        manifesttrackingnumber,
        TRANSPORTERLINENUMBER,
        TRANSPORTERNAME,
        TRANSPORTEREPAID,
        -- 按运单分组，计算每组的最大行号
        MAX(TRANSPORTERLINENUMBER) OVER (PARTITION BY manifesttrackingnumber) AS max_line_number
    FROM TRANSPORTER
)
SELECT 
    manifesttrackingnumber,
    -- 提取第1个运输商
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 1 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `First Transporter`,
    -- 提取第2-9个运输商
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 2 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 2`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 3 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 3`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 4 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 4`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 5 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 5`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 6 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 6`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 7 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 7`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 8 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 8`,
    MIN(CASE WHEN TRANSPORTERLINENUMBER = 9 THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Transporter 9`,
    -- 提取最后一个运输商（匹配提前计算好的最大行号）
    MIN(CASE WHEN TRANSPORTERLINENUMBER = max_line_number THEN CONCAT(TRIM(TRANSPORTERNAME), ' (', TRIM(TRANSPORTEREPAID), ')') END) AS `Final Transporter`
FROM TransporterWithMaxLine
GROUP BY manifesttrackingnumber;

说明： #

• 用CTETransporterWithMaxLine给每个manifesttrackingnumber（运单跟踪号）计算出对应的最大TRANSPORTERLINENUMBER，窗口函数MAX() OVER (PARTITION BY ...)不会触发聚合嵌套问题，它是在分组后逐行计算值。
• 透视时用MIN()（或MAX()，因为每个行号对应唯一一条记录）提取对应位置的运输商信息，避免空值干扰。
• 如果某个运单的运输商行号不足9个，对应的列会显示NULL，可以用COALESCE(..., '')替换成空字符串或其他默认值。

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