如何对员工岗位源表执行Pivot行转列查询（含动态实现需求）

一、固定列数实现（单岗位最多4名员工场景） #

采用开窗函数加聚合判断的写法，兼容性覆盖所有主流关系型数据库，可灵活调整输出字段格式：

1. 基础逻辑说明 #

先通过ROW_NUMBER()开窗给同岗位下的员工生成1~N的序号，再通过CASE WHEN结合聚合函数按序号提取对应员工信息，最终按岗位分组去重。

2. 单字段拼接输出（格式：员工号,姓名,状态） #

WITH ranked_emp AS (
    SELECT 
        Position,
        EmpNumber,
        Name,
        Status,
        -- 同岗位按员工号排序生成序号，可根据需求调整排序规则
        ROW_NUMBER() OVER(PARTITION BY Position ORDER BY EmpNumber) AS emp_seq
    FROM 你的源表名
)
SELECT 
    Position,
    MAX(CASE WHEN emp_seq = 1 THEN CONCAT_WS(',', EmpNumber, Name, Status) END) AS Emp1,
    MAX(CASE WHEN emp_seq = 2 THEN CONCAT_WS(',', EmpNumber, Name, Status) END) AS Emp2,
    MAX(CASE WHEN emp_seq = 3 THEN CONCAT_WS(',', EmpNumber, Name, Status) END) AS Emp3,
    MAX(CASE WHEN emp_seq = 4 THEN CONCAT_WS(',', EmpNumber, Name, Status) END) AS Emp4
FROM ranked_emp
GROUP BY Position
ORDER BY Position;

3. 仅输出姓名（匹配示例输出） #

将上述SQL中CASE WHEN里的拼接逻辑替换为Name即可：

MAX(CASE WHEN emp_seq = 1 THEN Name END) AS Emp1

4. 全属性拆分为多列输出 #

每个员工的信息拆分为独立列，示例格式为Emp1_Number、Emp1_Name、Emp1_Status：

WITH ranked_emp AS (
    SELECT 
        Position,
        EmpNumber,
        Name,
        Status,
        ROW_NUMBER() OVER(PARTITION BY Position ORDER BY EmpNumber) AS emp_seq
    FROM 你的源表名
)
SELECT 
    Position,
    MAX(CASE WHEN emp_seq = 1 THEN EmpNumber END) AS Emp1_Number,
    MAX(CASE WHEN emp_seq = 1 THEN Name END) AS Emp1_Name,
    MAX(CASE WHEN emp_seq = 1 THEN Status END) AS Emp1_Status,
    MAX(CASE WHEN emp_seq = 2 THEN EmpNumber END) AS Emp2_Number,
    MAX(CASE WHEN emp_seq = 2 THEN Name END) AS Emp2_Name,
    MAX(CASE WHEN emp_seq = 2 THEN Status END) AS Emp2_Status,
    MAX(CASE WHEN emp_seq = 3 THEN EmpNumber END) AS Emp3_Number,
    MAX(CASE WHEN emp_seq = 3 THEN Name END) AS Emp3_Name,
    MAX(CASE WHEN emp_seq = 3 THEN Status END) AS Emp3_Status,
    MAX(CASE WHEN emp_seq = 4 THEN EmpNumber END) AS Emp4_Number,
    MAX(CASE WHEN emp_seq = 4 THEN Name END) AS Emp4_Name,
    MAX(CASE WHEN emp_seq = 4 THEN Status END) AS Emp4_Status
FROM ranked_emp
GROUP BY Position
ORDER BY Position;

二、动态列数实现（应对单岗位员工数超过4的场景） #

核心逻辑是先统计单岗位的最大员工数，自动生成对应数量的查询列，再拼接为完整SQL执行，以下为MySQL环境的示例，其他数据库仅动态SQL执行语法略有差异，核心逻辑一致：

-- 1. 统计最大员工序号，自动生成列查询片段
SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN emp_seq = ', emp_seq, ' THEN CONCAT_WS(\',\', EmpNumber, Name, Status) END) AS Emp', emp_seq
    ) ORDER BY emp_seq
) INTO @sql
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY Position ORDER BY EmpNumber) AS emp_seq
    FROM 你的源表名
) t;

-- 2. 拼接完整查询SQL
SET @full_sql = CONCAT(
    'WITH ranked_emp AS (
        SELECT Position, EmpNumber, Name, Status, ROW_NUMBER() OVER(PARTITION BY Position ORDER BY EmpNumber) AS emp_seq
        FROM 你的源表名
    )
    SELECT Position, ', @sql, ' 
    FROM ranked_emp
    GROUP BY Position
    ORDER BY Position;'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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