Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Oracle Pivot

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
Best 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.
Answers
-
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.
-
Thank you , it worked perfectly.