I have a table that holds timetracking data of users and looks somewhat like this:
projectid activityid worklocationid year week day hours
1 22 21 2014 37 09/16/2014 8
2 22 4 2014 37 09/15/2014 8.5
So if a user saves hours on one project for every day of the week, there are 5 rows in the database that only differ in the "day" and "hours" columns. I need to display this data on a webpage in a timetable format, without having to query the database for each cell to get the hours for a specific day. I'd like to generate a view that has one row per week for one project/activity/worklocation combination:
projectid activityid worklocationid year week mon tue wed thu fri sat sun total
1 22 21 2014 37 8 8.5 9 8 8 41.5
I tried solving this with subselects and putting the days and hours values into one column respectively, separated by semicolons, but failed miserably because the table has over one million records.
My current approach would be using an OLAP cube, but I'm facing an issue:
There might be one entry for one week, e.g. on Monday, or one each for Monday and Thursday, or five for each day of the week, or none at all. So if I want to save them in an ordered format, I have to determine which row belongs to which day of the week. Is there a way to do this that scales up to this large amount of records?
We're using Oracle 11g.
Please feel free to point out if I missed to write down additional information.
1 Answer 1
Look up PIVOT command in Oracle SQL reference manual, it is what you need to solve this problem. Over a million records shouldn't be a problem. Since you want day list as static, you can "pivot max(hours) as hrs for to_char(day,'DY') in ('SUN','MON'))" etc. Once you have the query, adjust for column heading appropriately, and generate a totals column. If this igoing to be regularly used, you can generate a MV using this logic and update it appropriately.
Here is a simple example (I just used a row generator for sample data)
with x as (select 'Test' typ, to_char(sysdate+level,'DY') dt
from dual connect by 1=1 and level <= 366)
select * from x pivot (count(*) as cnt
for dt in ('SUN' as sun,'MON' as mon,'TUE' as tue, 'WED' as wed,
'THU' as thu, 'FRI' as fri, 'SAT' as sat))