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

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

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).

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

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.

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.

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
/

Select all Open in new window

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

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

Select all 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

Select all 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

Select all Open in new window

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

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
/

Select all 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

Select all Open in new window

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

thank you this is exactly what I needed