Leah
asked on
Multiple Rows in a table for Separate Columns
These are the raw data:
Current Title Start Date End Date Job
No 1/1/2000 12/1/2001 Intern
No 2/2/2002 7/1/2003 Cashier
No 9/2/2003 11/2/2005 Teacher
No 9/5/2007 12/2/2010 Supervisor
No 3/5/2011 3/8/2013 Manager
No 4/2/2013 7/5/2015 Sr Manager
Yes 8/8/2015 Director
I need to output to "5 Columns "the Job- beginning with the current one - Director, Sr Manager, Manager, Supervisor, Teacher
Case
When "Current Title" is Yes OR "End-Date" is Null
Then "Current Job" is Director
When End Date <> Start Date - List all Jobs with the highest to the lowest up to 5
C--Users-jmorr006-Desktop-sampledat.xlsx
Current Title Start Date End Date Job
No 1/1/2000 12/1/2001 Intern
No 2/2/2002 7/1/2003 Cashier
No 9/2/2003 11/2/2005 Teacher
No 9/5/2007 12/2/2010 Supervisor
No 3/5/2011 3/8/2013 Manager
No 4/2/2013 7/5/2015 Sr Manager
Yes 8/8/2015 Director
I need to output to "5 Columns "the Job- beginning with the current one - Director, Sr Manager, Manager, Supervisor, Teacher
Case
When "Current Title" is Yes OR "End-Date" is Null
Then "Current Job" is Director
When End Date <> Start Date - List all Jobs with the highest to the lowest up to 5
C--Users-jmorr006-Desktop-sampledat.xlsx
try
with s as (
select CurrentTitle, StartDate, EndDate, Job,
row_number() over (partition by EmployeeID order by StartDate desc) rn
)
select s.*, c.CurrentTitle CurrentJob,
p2.CurrentTitle SecondJob,
p3.CurrentTitle ThirdJob,
p4.CurrentTitle FourthJob,
p5.CurrentTitle FifthJob
from s
left join s p2 on s.EmployeeID=p2.EmployeeID and p2.rn=2
left join s p3 on s.EmployeeID=p3.EmployeeID and p3.rn=3
left join s p4 on s.EmployeeID=p4.EmployeeID and p4.rn=4
left join s p5 on s.EmployeeID=p5.EmployeeID and p5.rn=5
where s.rn=1
Huseyin,
I recommend that you test your solutions before posting. There was enough sample data to at least test your query. After I corrected the syntax errors, incorrect column names, not projecting all the correct columns, and making up the non-existent EMPLOYEEID, this is the output:
If there is a significant amount of data in the table, then I would the creating a temporary table with no indexes and passing it 5 times would be very efficient. Places where I have worked, that would be a problem as there are hundreds of thousands of active employees and they have had multiple positions over the years.
I recommend that you test your solutions before posting. There was enough sample data to at least test your query. After I corrected the syntax errors, incorrect column names, not projecting all the correct columns, and making up the non-existent EMPLOYEEID, this is the output:
EMPLOYEEID CUR STARTDATE ENDDATE JOB RN CUR
---------- --- ------------------- ------------------- ---------- ---------- ---
SEC THI FOU FIF
--- --- --- ---
1 Yes 08/08/2015 00:00:00 Director 1 Yes
No No No No
That really looks nothing like what was requested.If there is a significant amount of data in the table, then I would the creating a temporary table with no indexes and passing it 5 times would be very efficient. Places where I have worked, that would be a problem as there are hundreds of thousands of active employees and they have had multiple positions over the years.
oops, sorry, we should get Job
@johnsone
and I dont have to time to create test data for every question...
but if you create one, welcome...
for EmployeeID column, I assumed op has one, otherwise it is not meaningful to have a table without any userinfo here, probably op just omitted it...
if op does not have any such column then
normally I assume asker knows how to put information here together to create their own solutions...
otherwise they should just read manuals or hire someone who knows what they are doing...
with s as (
select EmployeeID, CurrentTitle, StartDate, EndDate, Job,
row_number() over (partition by EmployeeID order by StartDate desc) rn
)
select s.*, c.Job CurrentJob,
p2.Job SecondJob,
p3.Job ThirdJob,
p4.Job FourthJob,
p5.Job FifthJob
from s
left join s p2 on s.EmployeeID=p2.EmployeeID and p2.rn=2
left join s p3 on s.EmployeeID=p3.EmployeeID and p3.rn=3
left join s p4 on s.EmployeeID=p4.EmployeeID and p4.rn=4
left join s p5 on s.EmployeeID=p5.EmployeeID and p5.rn=5
where s.rn=1
@johnsone
and I dont have to time to create test data for every question...
but if you create one, welcome...
for EmployeeID column, I assumed op has one, otherwise it is not meaningful to have a table without any userinfo here, probably op just omitted it...
if op does not have any such column then
with s as (
select CurrentTitle, StartDate, EndDate, Job,
row_number() over (order by StartDate desc) rn
)
select s.*, c.Job CurrentJob,
p2.Job SecondJob,
p3.Job ThirdJob,
p4.Job FourthJob,
p5.Job FifthJob
from s
left join s p2 on p2.rn=2
left join s p3 on p3.rn=3
left join s p4 on p4.rn=4
left join s p5 on p5.rn=5
where s.rn=1
normally I assume asker knows how to put information here together to create their own solutions...
otherwise they should just read manuals or hire someone who knows what they are doing...
Never assume that they can do something. It bites you on this site. People that post things want working answers, not stuff they have to modify. Even when they don't provide enough information. They get pretty mad when they have to change a column name because you didn't use your ESP to guess the correct column name. And there are a lot of people on here just starting out, so they actually can't make the modification without help.
Also, this could be already aggregated data and you are only looking for at data for one employee.
Never assume it gets you in trouble.
Your query still materialized the entire table into temporary space and has to pass it 5 times.
Also, to me, this looks like homework, so you shouldn't really be giving a full answer. That is why I left parts out of my original answer.
Also, this could be already aggregated data and you are only looking for at data for one employee.
Never assume it gets you in trouble.
Your query still materialized the entire table into temporary space and has to pass it 5 times.
Also, to me, this looks like homework, so you shouldn't really be giving a full answer. That is why I left parts out of my original answer.
When I see questions like this I also see the need to point out that in Oracle, you need to know the maximum number of columns a query can return.
Your examples data shows 5 jobs. I assume f someone has 20 jobs, you want 20 columns and if someone has 50 jobs, 50 columns?
You need to know the max number allowed and account for that many columns.
Now, if you want a single list of values like a CSV and the total length will always be less than 4000 characters, look into LISTAGG.
If you might exceed 4000 characters, you will need to writer our own or look at this Article where another Expert wrote the code for you:
https://www.experts-exchange.com/articles/9391/How-to-Create-User-Defined-Aggregates-in-Oracle.html
Look for Look for "concatclobagg".
Your examples data shows 5 jobs. I assume f someone has 20 jobs, you want 20 columns and if someone has 50 jobs, 50 columns?
You need to know the max number allowed and account for that many columns.
Now, if you want a single list of values like a CSV and the total length will always be less than 4000 characters, look into LISTAGG.
If you might exceed 4000 characters, you will need to writer our own or look at this Article where another Expert wrote the code for you:
https://www.experts-exchange.com/articles/9391/How-to-Create-User-Defined-Aggregates-in-Oracle.html
Look for Look for "concatclobagg".
And when I see:
up to 5in the original post, I believe that they only want 5 regardless of how many there are.
Agreed but I've seen many of these types of questions where the original requirement doesn't actually meet reality.
Given the framing of the question, I know people that have held more than 5 positions over a career.
If it is more of a homework-theory question, now they know there is a limitation moving forward?
Given the framing of the question, I know people that have held more than 5 positions over a career.
If it is more of a homework-theory question, now they know there is a limitation moving forward?
Who knows. They presented 7 rows of sample data of which 5 should be displayed. I would think the real limit is 5.
ASKER
Thank you all experts- Thanks for your understanding
Yes, I only need a maximum 5 job titles even if you have 20. Attached is the updated data for 1 person only. Thanks!
UpdatedData.xlsx
Yes, I only need a maximum 5 job titles even if you have 20. Attached is the updated data for 1 person only. Thanks!
UpdatedData.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes- This it is. Thanks
Open in new window