Oracle - Pivot Aggregate function results

Question

I have table where user daily activities are saved, and now I have to present these activities in report in following form.

I have written a query,

select ua.UserID, count(ua.UserID), 
(select count(*) form user_activities t1 where t1.UserID = ua.UserID and ActivityID = 1 and t1.Date = ua.Date group by t1.UserID, t1.ActivityID, t1.Date) as Meeting,
(select count(*) form user_activities t1 where t1.UserID = ua.UserID and ActivityID = 2 and t1.Date = ua.Date group by t1.UserID, t1.ActivityID, t1.Date) as Training,
ua.Date
from user_activities ua
group by ua.UserID, ua.ActivityID, ua.Date

But I know it is not an efficient one and activity IDs are hardcoded and in future there can be new type of activities.

Can you guide me to make it more dynamic and efficient?

Thanks

Possible duplicate of [Dynamic pivot in oracle sql](http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) — sstan, Dec 28 '15 at 18:57

fatihn · Answer 1 · 2015-12-28T18:23:26.420

2

Try following query;

select ua.UserID,
       ua.adate,
       count(1),
       sum(decode(ua.activityid, 1, 1, 0)) meeting,
       sum(decode(ua.activityid, 2, 1, 0)) training,
       sum(decode(ua.activityid, 3, 1, 0)) other1,
       sum(decode(ua.activityid, 4, 1, 0)) other2
  from user_activities ua
 group by ua.UserID, ua.adate
 order by ua.UserID, ua.adate

edited Dec 28 '15 at 18:23

answered Dec 28 '15 at 18:09

fatihn

137
1
8

1

By the way there is not any other way except hard coding your activity types. You are listing horizontal data rows as vertical, so you need decode statements with activity type ids. – fatihn Dec 28 '15 at 18:47

score 2 · Accepted Answer · answered Dec 28 '15 at 18:53

You may use PIVOT, to add the total activities use in a helper subquery an analytic function.

with tab2 as (
select USERID, ACTIVITYID, TRANS_DATE,
count(*) over (partition by USERID, TRANS_DATE) as total_activities 
from tab)
select * from tab2
PIVOT (count(*) for (activityId) in
('Meeting' as "MEETING",
'Outdoor' as "OUTDOOR",
'Training' as "TRAINING"))
;

returns

   USERID TRANS_DATE        TOTAL_ACTIVITIES    MEETING    OUTDOOR   TRAINING
---------- ----------------- ---------------- ---------- ---------- ----------
         1 28.12.15 00:00:00                2          0          1          1 
         3 28.12.15 00:00:00                2          1          1          0 
         2 28.12.15 00:00:00                2          2          0          0

Unfortunately a static select can't react to a new activities and automatically add a column.

You must update the list in the PIVOT for clause by adding a line for the new activity.

You may use this supporting query to create the actual list (remove the last comma)

select distinct ''''||activityId ||''' as "'||activityId||'",' from tab order by 1;

Marmite and Fatihn thanks for your efforts, both queries are simpler then mine and provide the expected results, but which one is more efficient, as of now there are a couple of hundred records, but the the table size will increase with time — user3141985, Dec 29 '15 at 04:43
@user3141985 I don't see some systematic difference in efficiency (performance). Both queries use a `GROUP BY` mechanism under the cover. I'd argue that PIVOT is more compact and easier to maintain (while adding new values) - but this is of course debatable.. — Marmite Bomber, Dec 29 '15 at 10:38

Oracle - Pivot Aggregate function results

2 Answers2