Hide this madness

Nifty Oracle List Aggregation Utility

I recently stumbled upon a nifty undocumented Oracle utility function that I thought others may also find useful.

The daycare my son goes to has an Oracle database for tracking kids’ various daily activities, one of which is about learning. The raw data looks something like the following in a table called activity_log.

          Kid         A_Date             Session                Activity
          ---------------------------------------------------------------------
          Tommy       10/20/2010         Morning A               Math
          Tommy       10/20/2010         Morning B               English
          Tommy       10/20/2010         Afternoon               French
          Karen       10/20/2010         Morning A               Drawing
          Karen       10/20/2010         Morning B               French
          Jacob       10/20/2010         Morning A               Math
          Jacob       10/20/2010         Morning B               French
          Jacob       10/20/2010         Afternoon               English
          Jennifer    10/20/2010         Morning A               Blocks
          Jennifer    10/20/2010         Morning B               Drawing
          Jennifer    10/20/2010         Afternoon               French
          Jennifer    10/20/2010         Extended Care           Dancing

Management at the daycare needs to analyze the data each week to help plan out the activities for each kids for the following week and make sure the kids’ activities are balanced on a monthly basis. I was helping the daycare with one of the reports that lists the number of kids that participate in the same set of activities on a given date. The order of the activities in the day is not important for this report.

I could easily code a procedure that queries each kid’s activities on a date, recognizes and sums up the ones that share the same set of activities. But as “lazy” a developer as I am, my first inclination was to do this in a single sql query without writing any custom functions. WM_CONCAT solved my problem.

Oracle introduced Workspace Manager (OWM) in 9i. (I certainly have my two cents worth on OWM, but that could be a more elaborated discussion for a later time…) With OWM, a default schema called WMSYS is installed by default, which owns packages, procedures and functions relating to OWM functionality. WM_CONCAT is a function owned by WMSYS. However, a public synonym with the same name makes it accessible to other schemas. Given a list of records associated with a specific value, this function aggregates data from the list of records into a single record. Applying the following query on activity_log renders the subsequent result:

select kid, wm_concat(activity) as activities
from (select * from activity_log where a_date = '10/20/2010' order by activity)
group by kid
          KID	          ACTIVITIES
          --------------------------------------------
          Jacob	          English,Math,French
          Jennifer	  Blocks,French,Drawing,Dancing
          Karen	          Drawing,French
          Tommy	          English,Math,French

To achieve what I set out to do, I simply used the above query results as a temp table from which I count up the number of kids for each unique set of activities.

select activities, count(*) as number_of_kids
from (select kid, wm_concat(activity) as activities
      from (select * from activity_log where a_date = '10/20/2010' order by activity)
     group by kid) 
group by activities
          ACTIVITIES	                       NUMBER_OF_KIDS
          -------------------------------------------------------
          English,Math,French                        2
          Blocks,French,Drawing,Dancing              1
          Drawing,French                             1

Voilà.

If you work with 11g release 2, it released a new aggregation function, LISTAGG, that works similarly as WM_CONCAT.

select kid, listagg(activity, ',') within group (order by activity) as activities
from activity_log
group by kid
          KID	               ACTIVITIES
          --------------------------------------------------
          Jacob	               English,French,Math
          Jennifer	       Blocks,Dancing,Drawing,French
          Karen	               Drawing,French
          Tommy	               English,French,Math
  1. I’d like to hear more about Oracle OWM…

    Feb 8th, 2013

Add a comment

Comment feed
The better to greet you with
No one will ever see this
Your pride and joy
The reason this comment form exists

The crew behind ASOT

We're a team of interactive, software, and business intelligence experts skilled in the design, construction, and management of online enterprise systems.

Visit The Jonah Group site

Get in touch with us