asked on create a oracle 10g query that dynamically creates columns based on a set of parameters
Guys, Not sure if this is possible but I am gonna ask away and see where I get.
I would like to be able to create a matrix of employees and there holidays in a query that can easily show who is on holiday at any point in time the paramaters are the start_date and End_Date then what it would do is create the columns that are between the parameters what the table would show is:-
parameter 1 = Start date 01/06/2015
parameter2 = End date 30/06/2015
=====These are static=== ===================dynamic
Employee, Emp_No,office, 01/06/2015,02/06/2015,03/0
the first 3 columns are static and will always show.
Any ideas on how to do this as I undesrtand in oracle 11g I could use a PIVOT function but as this is Oracle 10g that's not an option.
Regards
I would like to be able to create a matrix of employees and there holidays in a query that can easily show who is on holiday at any point in time the paramaters are the start_date and End_Date then what it would do is create the columns that are between the parameters what the table would show is:-
parameter 1 = Start date 01/06/2015
parameter2 = End date 30/06/2015
=====These are static=== ===================dynamic
Employee, Emp_No,office, 01/06/2015,02/06/2015,03/0
the first 3 columns are static and will always show.
Any ideas on how to do this as I undesrtand in oracle 11g I could use a PIVOT function but as this is Oracle 10g that's not an option.
Regards
Oracle DatabaseSQL
Last Comment
DarrenJackson
I don't see how dynamic SQL will help this.
To me this looks like a classic case of the LISTAGG function. However, that isn't available in 10. I will direct you to this question -> https://www.experts-exchange.com/questions/24120361/Simple-Query.html Where 2 alternatives are presented. They should both work in 10.
To me this looks like a classic case of the LISTAGG function. However, that isn't available in 10. I will direct you to this question -> https://www.experts-exchange.com/questions/24120361/Simple-Query.html Where 2 alternatives are presented. They should both work in 10.
To me this looks like a classic case of the LISTAGG functionLISTAGG is limited considering the length of the concatenated string (so you'll run into trouble when dealing with many columns and/or long column names).
ASKER
looking at the example from johnstone this has potential I will need to look at this Alexander your proposal is looking quite complex for such a simpleton like me.
But I will take a look anyhow.
Thankyou
But I will take a look anyhow.
Thankyou
Concatenating dates into a comma separated list is unlikely to exceed the limitations of any function. At a maximum of 11 characters each and a 4000 character limit, is a list of 363 entries. If anyone is getting that many holidays, I want to know where they work.
The solution is based on the desired results.
Do you want actual columns or ONE column with comma separated values?
Those are two completely different requirements.
Actual columns is complex to do.
If you have a known maximum of possible columns, then it becomes slightly easier but the query would always return that many columns.
CSV is pretty easy as you can see by the links above.
Do you want actual columns or ONE column with comma separated values?
Those are two completely different requirements.
Actual columns is complex to do.
If you have a known maximum of possible columns, then it becomes slightly easier but the query would always return that many columns.
CSV is pretty easy as you can see by the links above.
ASKER
I would prefer columns ( I know I'm a Pain )
The returned results are per month so there would only be a maximum of 31 columns + the fixed columns.
I think the example I created is misleading everyone into thinking I wanted a csv file output the dates I used are columns and I just added a comma to make it easy for everyone to read.
The returned results are per month so there would only be a maximum of 31 columns + the fixed columns.
I think the example I created is misleading everyone into thinking I wanted a csv file output the dates I used are columns and I just added a comma to make it easy for everyone to read.
Try this. It only has the first 7 days but you get the idea:
/*
drop table tab1 purge;
create table tab1(emp_no number, holiday date);
insert into tab1 values(1,to_date('05/31/2015','MM/DD/YYYY'));
insert into tab1 values(1,to_date('06/01/2015','MM/DD/YYYY'));
insert into tab1 values(1,to_date('06/15/2015','MM/DD/YYYY'));
insert into tab1 values(1,to_date('07/01/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('05/31/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('06/01/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('06/02/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('06/03/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('06/04/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('07/01/2015','MM/DD/YYYY'));
insert into tab1 values(3,to_date('01/02/2025','MM/DD/YYYY'));
commit;
*/
select emp_no,
min(case when rn=1 then holiday end) day1,
min(case when rn=2 then holiday end) day2,
min(case when rn=3 then holiday end) day3,
min(case when rn=4 then holiday end) day4,
min(case when rn=5 then holiday end) day5,
min(case when rn=6 then holiday end) day6,
min(case when rn=7 then holiday end) day7
from (
select emp_no, holiday, row_number() over(partition by emp_no order by holiday asc) rn
from tab1
where holiday >= to_date('06/01/2015','MM/DD/YYYY') and holiday < to_date('06/30/2015','MM/DD/YYYY')+1
)
group by emp_no
/
ASKER
slightwv thanks for the example
The database I am working in has a DATE_FROM and DATE_TO to work with the example you have given just has the 1 date.
to be able to show who is off for a period of time I would need to be able to show who is off for the days that encompass the date_from and date_to.
as in some kind of matrix
The database I am working in has a DATE_FROM and DATE_TO to work with the example you have given just has the 1 date.
to be able to show who is off for a period of time I would need to be able to show who is off for the days that encompass the date_from and date_to.
as in some kind of matrix
Then I missed your requirements.
Let's make sure we have all the requirements:
-Your table has the DATE_FROM and DATE_TO for each employee.
-You provide a beginning date and ending date.
-You want a list of individual days for each employee that fall between the dates you provide.
Go given a emp_holiday table like (using DD/MM/YYYY format):
Parameter dates of: 01/06/2015 and 30/06/2015
You want output like:
OR
Do you want ALL columns in the date range and a 'check' for the employee
something like:
sorry but the spacing is off a little....
Let's make sure we have all the requirements:
-Your table has the DATE_FROM and DATE_TO for each employee.
-You provide a beginning date and ending date.
-You want a list of individual days for each employee that fall between the dates you provide.
Go given a emp_holiday table like (using DD/MM/YYYY format):
emp_no date_from date_to
1 05/06/2015 07/06/2015
2 29/06/2015 01/07/2015
Parameter dates of: 01/06/2015 and 30/06/2015
You want output like:
1 05/06/2015 06/06/2015 07/06/2015
2 29/06/2015 30/06/2015
OR
Do you want ALL columns in the date range and a 'check' for the employee
something like:
emp_no month_day_1 month_day_2 ... month_day_6 month_day_7 ... month_day_31
1 off off
2 off
sorry but the spacing is off a little....
Can you post the structure (i.e. do a describe on) of the table that has the employee data? What field in that table exists that indicate an employee is on some type of leave? Some sample data along with what you would like to see as output would be most helpful.
I'm going to guess you want the data like option I posted above.
I borrowed the days between two dates code from:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:14582643282111
Here is what came up with (again for only 7 days but you can ad the rest):
Here are my results:
I borrowed the days between two dates code from:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:14582643282111
Here is what came up with (again for only 7 days but you can ad the rest):
drop table tab1 purge;
create table tab1(emp_no number, date_from date, date_to date);
insert into tab1 values(1,to_date('05/31/2015','MM/DD/YYYY'),to_date('06/05/2015','MM/DD/YYYY'));
insert into tab1 values(2,to_date('06/25/2015','MM/DD/YYYY'),to_date('07/02/2015','MM/DD/YYYY'));
insert into tab1 values(3,to_date('01/02/2025','MM/DD/YYYY'),to_date('01/02/2025','MM/DD/YYYY'));
commit;
select emp_no,
min(case when rn=1 then holiday end) day1,
min(case when rn=2 then holiday end) day2,
min(case when rn=3 then holiday end) day3,
min(case when rn=4 then holiday end) day4,
min(case when rn=5 then holiday end) day5,
min(case when rn=6 then holiday end) day6,
min(case when rn=7 then holiday end) day7
from (
select emp_no,
holiday,
row_number() over(partition by emp_no order by holiday) rn
from (
select emp_no,
date_from+COLUMN_VALUE-1 holiday
from tab1, TABLE(
cast(
multiset(
select level l
from dual connect by level <= date_to-date_from+1
) as sys.odciNumberList
)
)
)
where holiday >= to_date('06/01/2015','MM/DD/YYYY') and
holiday < to_date('06/30/2015','MM/DD/YYYY')+1
)
group by emp_no
/
Here are my results:
EMP_NO DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 06/01/2015 06/02/2015 06/03/2015 06/04/2015 06/05/2015
2 06/25/2015 06/26/2015 06/27/2015 06/28/2015 06/29/2015 06/30/2015
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
omg slightw your option 2 is exactly what I am after
this is great thank you sooo much for this
Guys I appreciate you all taking the time to assist but from the code supplied by slightw I think this is deserved as the answer.
Thank you all
this is great thank you sooo much for this
Guys I appreciate you all taking the time to assist but from the code supplied by slightw I think this is deserved as the answer.
Thank you all
ASKER
thank you this is exactly what I needed
http://www.oracle-developer.net/display.php?id=422