create a oracle 10g query that dynamically creates columns based on a set of parameters

DarrenJackson
DarrenJackson used Ask the Experts™
on
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===     ===================dynamically created============================
Employee, Emp_No,office,    01/06/2015,02/06/2015,03/06/2015..........28/06/2015, 29/06/2015, 30/06/2015


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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Maybe using "method 4 dynamic SQL in PL/SQL" could help, you should take a look:

http://www.oracle-developer.net/display.php?id=422
johnsoneSenior Oracle DBA

Commented:
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 -> http://www.experts-exchange.com/Database/Oracle/Q_24120361.html  Where 2 alternatives are presented.  They should both work in 10.
To me this looks like a classic case of the LISTAGG function
LISTAGG  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).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
johnsoneSenior Oracle DBA

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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
/

Open in new window

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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):
emp_no         date_from        date_to
1                    05/06/2015      07/06/2015
2                    29/06/2015      01/07/2015

Open in new window


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

Open in new window



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

Open in new window


sorry but the spacing is off a little....
awking00Information Technology Specialist

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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):
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
/

Open in new window


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

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
For option two, here is what I came up with:
select emp_no, 
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 1 then 'off' end) day1,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 2 then 'off' end) day2,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 3 then 'off' end) day3,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 4 then 'off' end) day4,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 5 then 'off' end) day5,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 6 then 'off' end) day6,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 7 then 'off' end) day7,
	--...
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 27 then 'off' end) day27,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 28 then 'off' end) day28,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 29 then 'off' end) day29,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 30 then 'off' end) day30,
	min(case when to_number(to_char(emp_holiday.holiday,'DD')) = 31 then 'off' end) day31
from
	(
	select emp_no,
		holiday
	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
	) emp_holiday
group by emp_no
/

Open in new window


Output:
    EMP_NO DAY DAY DAY DAY DAY DAY DAY DAY DAY DAY DAY DAY
---------- --- --- --- --- --- --- --- --- --- --- --- ---
         1 off off off off off
         2                             off off off off

Open in new window

Author

Commented:
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

Author

Commented:
thank you this is exactly what I needed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial