Combining data of multiple rows in view

olaporacleoracle-11gview

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.

Best Answer

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))

Related Solutions

Problems with generating Audit SQL

Looks like you need a GROUP BY. Your query above done with a GROUP BY would look like this:

SELECT to_char(auditdate,'YYYY'), count(*) 
FROM AuditLog GROUP BY to_char(auditdate,'YYYY');

A similar query can be used for other groupings. For example, here would be a count for each day:

SELECT to_char(auditdate,'MM/DD/YYYY'), count(*) 
FROM AuditLog GROUP BY to_char(auditdate,'MM/DD/YYYY');

To do this for each PageTitle, simply add PageTitle like this:

SELECT PageTitle , to_char(auditdate,'MM/DD/YYYY'), count(*) 
FROM AuditLog GROUP BY PageTitle, to_char(auditdate,'MM/DD/YYYY');

To pivot the data as you have shown gets a bit more difficult particularly on 10g where there is no PIVOT. Here is one way to do it on 10g:

SELECT PageTitle 
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/22/2012',1,NULL)) Oct_22
   , Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/23/2012',1,NULL)) Oct_23
FROM AuditLog GROUP BY PageTitle;

Of course this requires entering all the dates. You can make this a bit easier by generating the query using a query such as this:

SELECT 'SELECT PageTitle ' SQL FROM dual
UNION ALL
SELECT DISTINCT '    , Sum(DECODE(to_char(AuditDate,''MM/DD/YYYY''),'''
   || to_char(AuditDate,'MM/DD/YYYY') || ''',1,NULL)) ' || to_char(AuditDate,'Mon_DD')
FROM AuditLog
UNION ALL
SELECT 'FROM AuditLog GROUP BY PageTitle;' FROM dual;

Here is the sample data I used:

drop table auditlog;
create table AuditLog as (select sysdate auditdate, 'Page 1' PageTitle from dual);
insert into auditlog (select sysdate, 'Page 1' from dual);
insert into auditlog (select sysdate, 'Page 2' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 3' from dual);
commit;

With Clause vs View of the same name

This is a bug, fixed in the July 2014 CPU - See Oracle Support document 1666884.1.

Easily reproduced with the following:

create view v1 as ( select 'view' as vvv from dual );

create view v2 as ( select * from v1 ); 

with v1 as ( select 'cte' as vvv from dual) select * from v2;

Best Answer

Related Solutions

Problems with generating Audit SQL

With Clause vs View of the same name

Related Question