T-SQL基于MIN/MAX实现无需固定IN值的PIVOT查询问题咨询

问题原因 #

T-SQL 中 PIVOT 运算符的 IN 子句必须传入静态的固定值列表，不支持直接写子查询动态获取值，这是你现有代码执行报错的直接原因。
另外你的 PIVOT 逻辑也不符合需求：你要返回的是对应年限的员工编号，所以聚合函数应该作用于Worker字段，而不是Years of Experience。

推荐实现方案（无需PIVOT，性能更优） #

用窗口函数排序+条件聚合即可直接得到目标结果：

SELECT 
    [Job Description],
    MAX(CASE WHEN rn_desc = 1 THEN Worker END) AS [Most Experienced],
    MAX(CASE WHEN rn_asc = 1 THEN Worker END) AS [Least Experienced]
FROM (
    SELECT 
        Worker,
        [Job Description],
        -- 同岗位按工作年限倒序排序，1为最高年限
        ROW_NUMBER() OVER (PARTITION BY [Job Description] ORDER BY [Years of Experience] DESC) AS rn_desc,
        -- 同岗位按工作年限正序排序，1为最低年限
        ROW_NUMBER() OVER (PARTITION BY [Job Description] ORDER BY [Years of Experience] ASC) AS rn_asc
    FROM Q2
) AS t
WHERE rn_desc = 1 OR rn_asc = 1
GROUP BY [Job Description]

如果你坚持用PIVOT实现 #

你需要先给数据打上「最高/最低年限」的标签，再用固定列名做PIVOT：

SELECT 
    [Job Description],
    [Most Experienced],
    [Least Experienced]
FROM (
    SELECT 
        Worker,
        [Job Description],
        CASE 
            WHEN rn_desc = 1 THEN 'Most Experienced' 
            WHEN rn_asc = 1 THEN 'Least Experienced' 
        END AS exp_type
    FROM (
        SELECT 
            Worker,
            [Job Description],
            ROW_NUMBER() OVER (PARTITION BY [Job Description] ORDER BY [Years of Experience] DESC) rn_desc,
            ROW_NUMBER() OVER (PARTITION BY [Job Description] ORDER BY [Years of Experience] ASC) rn_asc
        FROM Q2
    ) AS t
    WHERE rn_desc = 1 OR rn_asc = 1
) AS SourceTable
PIVOT (
    MAX(Worker)
    FOR exp_type IN ([Most Experienced],[Least Experienced])
) AS PivotTable

补充说明 #

如果你后续有动态生成透视列的需求，可以通过拼接动态SQL的方式实现，但你当前需求的输出列是固定的，不需要用动态SQL，上面两种写法都可以直接得到你给出的目标结果。

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