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===     ===================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
DarrenJacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***Alex140181***]Software DeveloperCommented:
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 DBACommented:
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.
Alex [***Alex140181***]Software DeveloperCommented:
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DarrenJacksonAuthor 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 DBACommented:
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.
slightwv (䄆 Netminder) 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.
DarrenJacksonAuthor 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.
slightwv (䄆 Netminder) 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

DarrenJacksonAuthor 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
slightwv (䄆 Netminder) 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 SpecialistCommented:
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.
slightwv (䄆 Netminder) 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

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor 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
DarrenJacksonAuthor Commented:
thank you this is exactly what I needed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.