How to create a new column for each value associated with a specific ID in SQL query-CodePudding

I'm trying to extract information about certain people based on their ID and what job they've applied for. Each job has a specific ID and each person has a specific ID. You can ignore the join and from parts of the code below.

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;

When I run code like this it returns a table like so

Person ID	Job ID
1	142
1	631
2	108
3	135
3	213
3	534

I'm wondering how to change my code to make it look like this, with a different column for each job they've applied for. It varies how many jobs a person has applied for

Person_ID	Job_ID1	Job_ID2	Job_ID3
1	142	631
2	108
3	534	135	213

Any help appreciated, thanks!

CodePudding user response：

Try:

order by person_id, job_id

GROUP BY Person_ID

CodePudding user response：

You can generate a ROW_NUMBER and then can do conditional aggregation on this column -

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;