SQL Analytics in every day APEX

Looking for way to apply analytical functions to your APEX applications?

I had a classic report where I wanted to dynamically source the column headers from counts in the database (values in brackets).

The ability to do this has been a feature of APEX for a while, but this was the first time I did it in APEX 5.0.

Customise headers via Region attributes

With Connor's recent spate of analytics videos,  I thought I'd mention this use case where LISTAGG() was perfectly apt.

A basic query like this will return a grouped count.

select count(*), catgy
from some_categories
group by catgy

  COUNT(*) CATGY_CODE
---------- ----------
         5 CATGY1    
         5 CATGY3    
         1 CATGY5    
         7 CATGY2    
         1 CATGY4    
         1 CATGY6    

 6 rows selected 

But the string needs to look like
Rep:Catgy1 (2):Catgy2 (4):...

So I need to transpose those rows into a colon delimited string. Here's how you can do it with SQL analytics.

declare
  lc_hdr  varchar2(512);
begin  
  select 'Rep:'||listagg(initcap(catgy)||' ('||count(*)||')' -- just build a fancy string
                        ,':') within group (order by catgy) -- concatenated by ':', listed in order of catgy
  into lc_hdr
  from some_categories
  group by catgy;

  return lc_hdr;
end anon;

An alternative may be bulk collecting the results into a PL/SQL array, then using apex_util.table_to_string(), but not when the problem can be solved with simple SQL ;p