Found
this cool script from a oracle blog.
SELECT
LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed",
"Thu", "Fri", "Sat"
FROM
(SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH, week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2)))
"Sat"
FROM
( select dt, (case when dt >=
to_date(to_char(dt,
'dd/')||'12/'||to_char(sysdate,'yyyy'),'dd/mm/yyyy') and wk = '01' then '53' else
wk end) week
from(
SELECT
TRUNC(SYSDATE,'y')-1+ROWNUM dt, TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM+1,'iw') wk
FROM
all_objects
WHERE
ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
GROUP
BY TO_CHAR(dt,'fmMonthfm YYYY'), week)
ORDER
BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);
