Oracle Pivot

user9311128

I have employee name and salary date fields in a table having only these two values. Need the salary date output in pivot row format, can anyone please help? Pasted the sample queries and the expected output format below.

create table testdaterows (empname varchar2(30), salary_dt date);

insert into testdaterows values ('peter', to_date('01/01/2021','MM/DD/YYYY'));

insert into testdaterows values ('peter', to_date('02/01/2021','MM/DD/YYYY'));

insert into testdaterows values ('peter', to_date('05/01/2021','MM/DD/YYYY'));

insert into testdaterows values ('rachael', to_date('01/10/2021','MM/DD/YYYY'));

insert into testdaterows values ('rachael', to_date('03/10/2021','MM/DD/YYYY'));

insert into testdaterows values ('rachael', to_date('04/10/2021','MM/DD/YYYY'));

insert into testdaterows values ('Anita', to_date('02/15/2021','MM/DD/YYYY'));

insert into testdaterows values ('Anita', to_date('03/15/2021','MM/DD/YYYY'));

commit;

select * from testdaterows;

Empname Salary_dt

peter 1/1/2021

peter 2/1/2021

peter 5/1/2021

rachael 1/10/2021

rachael 3/10/2021

rachael 4/10/2021

Anita 2/15/2021

Anita 3/15/2021

Expected Output:

--Thanks

Frank Kulash

Hi, @user9311128

Thanks for posting the sample data and results; that really helps!

If you want to use the SELECT ... PIVOT feature, then you need to generate another column that indicates in which column of the result set the data will go. Here's one way to do that:

WITH  data_to_pivot  AS
(
	SELECT  empname, salary_dt
	,	TO_CHAR (salary_dt, 'MON-YYYY') AS salary_mon
	FROM	testdaterows
	WHERE	salary_dt >= TO_DATE ('01/01/2021', 'MM/DD/YYYY') -- or whatever you need
	AND	salary_dt <  TO_DATE ('06/01/2021', 'MM/DD/YYYY')
)
SELECT	 *
FROM	 data_to_pivot
PIVOT	 (  MIN (salary_dt)
	 FOR salary_mon IN ( 'JAN-2021' AS jan
	    		   , 'FEB-2021' AS feb
	    		   , 'MAR-2021' AS mar
	    		   , 'APR-2021' AS apr
	    		   , 'MAY-2021' AS may
	    		   )
	 )
ORDER BY empname
;

As always, the number of columns in the result set and their names must be hard-coded. If you want them to depend on what data is found in the query, then you need dynamic SQL.

Results from your sample data:

EMPNAME    JAN        FEB        MAR        APR        MAY
---------- ---------- ---------- ---------- ---------- ----------
Anita                 2/15/2021  3/15/2021
peter      1/1/2021   2/1/2021                         5/1/2021
rachael    1/10/2021             3/10/2021 4/10/2021

What results do you want if there are two (or more) rows in the table for the same empname and month?

Is the order of rows in the result set significant? If so, explain what order you want.

Frank Kulash

Hi, @user9311128

Thanks for posting the sample data and results; that really helps!

If you want to use the SELECT ... PIVOT feature, then you need to generate another column that indicates in which column of the result set the data will go. Here's one way to do that:

WITH  data_to_pivot  AS
(
	SELECT  empname, salary_dt
	,	TO_CHAR (salary_dt, 'MON-YYYY') AS salary_mon
	FROM	testdaterows
	WHERE	salary_dt >= TO_DATE ('01/01/2021', 'MM/DD/YYYY') -- or whatever you need
	AND	salary_dt <  TO_DATE ('06/01/2021', 'MM/DD/YYYY')
)
SELECT	 *
FROM	 data_to_pivot
PIVOT	 (  MIN (salary_dt)
	 FOR salary_mon IN ( 'JAN-2021' AS jan
	    		   , 'FEB-2021' AS feb
	    		   , 'MAR-2021' AS mar
	    		   , 'APR-2021' AS apr
	    		   , 'MAY-2021' AS may
	    		   )
	 )
ORDER BY empname
;

As always, the number of columns in the result set and their names must be hard-coded. If you want them to depend on what data is found in the query, then you need dynamic SQL.

Results from your sample data:

EMPNAME    JAN        FEB        MAR        APR        MAY
---------- ---------- ---------- ---------- ---------- ----------
Anita                 2/15/2021  3/15/2021
peter      1/1/2021   2/1/2021                         5/1/2021
rachael    1/10/2021             3/10/2021 4/10/2021

What results do you want if there are two (or more) rows in the table for the same empname and month?

Is the order of rows in the result set significant? If so, explain what order you want.

user9311128

Thank you , it worked perfectly.