Avatar of DarrenJackson
DarrenJackson
Flag for United Kingdom of Great Britain and Northern Ireland 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===     ===================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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
DarrenJackson

8/22/2022 - Mon
Alex [***Alex140181***]

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
johnsone

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.
Alex [***Alex140181***]

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).
Your help has saved me hundreds of hours of internet surfing.
fblack61
DarrenJackson

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
johnsone

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)

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
DarrenJackson

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

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

DarrenJackson

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

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

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)

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DarrenJackson

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
DarrenJackson

ASKER
thank you this is exactly what I needed