You Asked
Hi Tom,
I need a alternative for PIVOT, i need to pass the date values dynamically. Date should fetch from current month first day to last day.
Result should be count of employees assigned to a shift on each day of the month. Please help me on this. Currently i have hardcoded the dates
SELECT SHIFT_ID, nvl(COUNT1,0) , nvl(COUNT2,0), nvl(COUNT3,0), nvl(COUNT4,0), nvl(COUNT5,0), nvl(COUNT6,0), nvl(COUNT7,0), nvl(COUNT8,0), nvl(COUNT9,0), nvl(COUNT10,0), nvl(COUNT11,0), nvl(COUNT12,0), nvl(COUNT13,0), nvl(COUNT14,0), nvl(COUNT15,0), nvl(COUNT16,0), nvl(COUNT17,0), nvl(COUNT18,0), nvl(COUNT19,0), nvl(COUNT20,0), nvl(COUNT21,0), nvl(COUNT22,0), nvl(COUNT23,0), nvl(COUNT24,0), nvl(COUNT25,0), nvl(COUNT26,0), nvl(COUNT27,0), nvl(COUNT28,0), nvl(COUNT29,0), nvl(COUNT30,0)
FROM ( SELECT B.SHIFT_ID, B.DUR, case when COUNT(A.EMPLID) is null then 0 else COUNT(A.EMPLID) end as COUNT_EMP
FROM PS_SCH_ASSIGN A, PS_SCH_CLND_VW B
WHERE A.SETID = B.SETID
AND A.SCH_ADHOC_IND = B.SCH_ADHOC_IND
AND A.SCHEDULE_ID = B.SCHEDULE_ID
AND A.ROTATION_ID = B.ROTATION_ID
AND B.SETID LIKE '%PHL'
AND B.DUR IN (SELECT (first_date+ LEVEL-1) calendar FROM (SELECT trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1) AS first_date , ADD_MONTHS(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 AS last_date FROM dual) CONNECT BY LEVEL<=last_date+1-first_date)
GROUP BY B.SHIFT_ID, B.DUR ORDER BY B.DUR,B.SHIFT_ID ASC ) X PIVOT (SUM(X.COUNT_EMP) FOR DUR IN('01-DEC-16' AS COUNT1,'02-DEC-16' AS COUNT2,'03-DEC-16' AS COUNT3,'04-DEC-16' AS COUNT4,'05-DEC-16' AS COUNT5,'06-DEC-16' AS COUNT6,'07-DEC-16' AS COUNT7,'08-DEC-16' AS COUNT8,'09-DEC-16' AS COUNT9,'10-DEC-16' AS COUNT10,'11-DEC-16' AS COUNT11,'12-DEC-16' AS COUNT12,'13-DEC-16' AS COUNT13,'14-DEC-16' AS COUNT14,'15-DEC-16' AS COUNT15,'16-DEC-16' AS COUNT16,'17-DEC-16' AS COUNT17,'18-DEC-16' AS COUNT18,'19-DEC-16' AS COUNT19,'20-DEC-16' AS COUNT20,'21-DEC-16' AS COUNT21,'22-DEC-16' AS COUNT22,'23-DEC-16' AS COUNT23,'24-DEC-16' AS COUNT24,'25-DEC-16' AS COUNT25,'26-DEC-16' AS COUNT26,'27-DEC-16' AS COUNT27,'28-DEC-16' AS COUNT28,'29-DEC-16' AS COUNT29,'30-DEC-16' AS COUNT30))
Thanks
Mohamed
and Chris said...
If you're always dealing with one month, instead of hardcoding the dates so you need to change them each month, you could extract the day. Then pivot on this:
create table t (
x date
);
insert into t
select trunc(sysdate, 'mm')+mod(rownum, 30)
from dual connect by level <= 50;
select *
from (select to_char(x, 'dd') dy from t)
pivot (count(*) for dy in (
'01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'11', '12', '13', '14', '15', '16', '17', '18', '19', '20',
'21', '22', '23', '24', '25', '26', '27', '28', '29', '30',
'31')
);
'01' '02' '03' '04' '05' '06' '07'
---------- ---------- ---------- ---------- ---------- ---------- ----------
'08' '09' '10' '11' '12' '13' '14'
---------- ---------- ---------- ---------- ---------- ---------- ----------
'15' '16' '17' '18' '19' '20' '21'
---------- ---------- ---------- ---------- ---------- ---------- ----------
'22' '23' '24' '25' '26' '27' '28'
---------- ---------- ---------- ---------- ---------- ---------- ----------
'29' '30' '31'
---------- ---------- ----------
1 2 2 2 2 2 2
2 2 2 2 2 2 2
2 2 2 2 2 2 2
1 1 1 1 1 1 1
1 1 0
Now you can leave your SQL the same each time!
If you don't like this, you could:
- Use dynamic SQL to create the pivot clause
- Use XML pivoting. This enables you to pivot on values in a subquery. But you get the results as XML...
For more on dynamic pivoting head to:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to#pivot And check the "Dynamic Column Lists" section.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment