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

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')

CodePudding user response：

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