Forum Stats

  • 3,750,345 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

Oracle Pivot

user9311128
user9311128 Member Posts: 16 Blue Ribbon

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

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    Accepted Answer

    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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    Accepted Answer

    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
  • user9311128
    user9311128 Member Posts: 16 Blue Ribbon

    Thank you , it worked perfectly.