如何为SQL查询中与特定ID关联的每个值创建新列

Question

我试图根据他们的ID和他们申请的工作提取有关某些人的信息。每个工作都有一个特定的ID，每个人都有一个特定的ID。您可以忽略下面代码的join和from部分。

select distinct
person_id "Person ID", 
job_id "Job ID"
from
job_table
join application_table on job_id = app_jobID
join person_table on app_id = per_appID
order by person_id, job_id;

当我运行这样的代码时，它返回如下的表

tbody> <<tr>12

个人ID	工作ID
1	142
	631
	108
3	135
3	213
3	534

您可以生成一个ROW_NUMBER，然后可以对该列进行条件聚合-

SELECT person_id,
MAX(CASE WHEN RN = 1 THEN job_id ELSE NULL END) Job_ID1,
MAX(CASE WHEN RN = 2 THEN job_id ELSE NULL END) Job_ID2,
MAX(CASE WHEN RN = 3 THEN job_id ELSE NULL END) Job_ID3
FROM (SELECT DISTINCT person_id "Person ID",
job_id "Job ID"
ROW_NUMBER() OVER(PARTITION BY person_id) RN
FROM job_table
JOIN application_table ON job_id = app_jobID
JOIN person_table ON app_id = per_appID) 
GROUP BY person_id
ORDER BY person_id, job_id;