employment data - Pivot table, placing all employment data in one row

user_8887556 0
Viewed 49

Hi so I have table as following

employee id job start_dt
1 abc 1/1/2021
1 def 5/1/2021
2 xyz 6/1/2021
2 rfd 8/1/2021
2 hgf 7/1/2021
2 esd 1/1/1999

I was wonder if there's a way I could pivot the table and layup all job and date at the same row,

employee id job_a start_dt_ a job_b start_dt_b job_c start_dt_c job_d start_dt_d job_e start_dt_e job_f start_dt_f
1 abc 1/1/2021 def 5/1/2021
2 xyz 6/1/2021 rfd 8/1/2021 hgf 7/1/2021 esd 1/1/1999

(table name 'JOB')

pivot oracle sql
1 Answers

You can use Conditional Aggregation along with ROW_NUMBER() Analytic function such as

WITH j AS
(  
 SELECT ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY start_dt) AS rn,
        j.*
   FROM job j
)
SELECT employee_id, 
       MAX(CASE WHEN rn = 1 THEN job END) AS job_a,
       MAX(CASE WHEN rn = 1 THEN start_dt END) AS start_dt_a,  
       MAX(CASE WHEN rn = 2 THEN job END) AS job_b,
       MAX(CASE WHEN rn = 2 THEN start_dt END) AS start_dt_b,
       MAX(CASE WHEN rn = 3 THEN job END) AS job_c,
       MAX(CASE WHEN rn = 3 THEN start_dt END) AS start_dt_c,
       MAX(CASE WHEN rn = 4 THEN job END) AS job_d,
       MAX(CASE WHEN rn = 4 THEN start_dt END) AS start_dt_d              
  FROM j
 GROUP BY employee_id
 ORDER BY employee_id

Demo

P.S. the logic for the sorting for current result set is not clear enough

user_58413060
Related