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).
/*
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
/
emp_no date_from date_to
1 05/06/2015 07/06/2015
2 29/06/2015 01/07/2015
1 05/06/2015 06/06/2015 07/06/2015
2 29/06/2015 30/06/2015
emp_no month_day_1 month_day_2 ... month_day_6 month_day_7 ... month_day_31
1 off off
2 off
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
/
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
http://www.oracle-developer.net/display.php?id=422