SQL查询中如何为指定ID关联的每个值分别创建独立新列

这个需求属于**动态透视（行转列）**场景，由于不同人员申请的职位数量不固定，核心实现逻辑分两步：先按人员维度给关联的每个职位编递增序号，再基于序号把行数据转成单独列。根据你是否能提前确定单人最多关联的职位数，可以选两种实现方案：

方案1：静态实现（已知单人最多关联职位数时推荐） #

如果提前统计过全量人员中最多申请N个职位，直接用聚合函数+条件判断即可实现，代码简单易维护，不需要写复杂的动态逻辑。
以你给出的示例数据（单人最多申请3个职位）为例，代码如下：

WITH person_job_rn AS (
    SELECT DISTINCT
        person_id,
        job_id,
        -- 按人员分组，为每个关联的职位按ID升序编序号，从1开始
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY job_id) AS rn
    FROM
        job_table
        JOIN application_table ON job_id = app_jobID
        JOIN person_table on app_id = per_appID
)
SELECT
    person_id AS "Person ID",
    MAX(CASE WHEN rn = 1 THEN job_id END) AS "Job_ID1",
    MAX(CASE WHEN rn = 2 THEN job_id END) AS "Job_ID2",
    MAX(CASE WHEN rn = 3 THEN job_id END) AS "Job_ID3"
    -- 后续如果出现单人申请更多职位的情况，按相同格式在这里追加rn=4、rn=5的判断即可
FROM person_job_rn
GROUP BY person_id
ORDER BY person_id;

执行后返回的结果和你预期完全一致，无对应职位的列会自动留空：

方案2：动态实现（职位数量不固定、不想手动维护列时使用） #

如果单人申请的职位数经常变动，不想每次新增职位数上限时修改SQL，可以用动态SQL自动识别最大关联职位数，动态生成对应数量的列，不需要手动维护列定义。
以PostgreSQL为例的实现代码：

DO $$
DECLARE
    max_job_count INT;
    dynamic_column_sql TEXT;
    final_query_sql TEXT;
BEGIN
    -- 第一步：统计所有人员中最多关联多少个职位，确定需要生成的列总数
    SELECT MAX(job_cnt) INTO max_job_count
    FROM (
        SELECT COUNT(DISTINCT job_id) AS job_cnt
        FROM job_table
        JOIN application_table ON job_id = app_jobID
        JOIN person_table ON app_id = per_appID
        GROUP BY person_id
    ) t;

    -- 第二步：自动生成所有Job_ID列对应的聚合逻辑语句
    SELECT string_agg(
        format('MAX(CASE WHEN rn = %s THEN job_id END) AS "Job_ID%s"', seq, seq),
        ', '
    ) INTO dynamic_column_sql
    FROM generate_series(1, max_job_count) seq;

    -- 第三步：拼接最终可执行的完整查询SQL
    final_query_sql := format('
        WITH person_job_rn AS (
            SELECT DISTINCT
                person_id,
                job_id,
                ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY job_id) AS rn
            FROM
                job_table
                JOIN application_table ON job_id = app_jobID
                JOIN person_table ON app_id = per_appID
        )
        SELECT
            person_id AS "Person ID",
            %s
        FROM person_job_rn
        GROUP BY person_id
        ORDER BY person_id
    ', dynamic_column_sql);

    -- 执行最终SQL返回结果，如果客户端不支持直接返回块内查询结果，可以打印final_query_sql后手动执行
    RAISE NOTICE '生成的查询SQL：%', final_query_sql;
    EXECUTE final_query_sql;
END $$;

如果使用MySQL、SQL Server等其他数据库，核心逻辑完全一致，只需要把动态SQL的语法替换成对应数据库的预处理/存储过程语法即可。

注意：标准SQL本身不支持查询结果动态返回不确定数量的列，动态列的实现本质是提前计算列数、拼接对应结构的SQL再执行，所有数据库的动态透视都是这个逻辑。

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