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