I am working in Oracle Apex 4.2. I have two tables:
I have simple report to build
select id, name, location_id from tablel1
-----------------------------------
| ID | NAME | PROJECT_ID |
-----------------------------------
| 1 | P1 | 23:45:56 |
| 2 | P2 | 23 |
| 3 | P3 | 45:65 |
-----------------------------------
------------------------------------------
| ID | NAME | SITE |
------------------------------------------
| 23 | Orlando | SITE1 |
| 45 | Arizona | SITE2 |
| 65 | Maimi | SITE3 |
------------------------------------------
However the problem I am having is that location_id holds only information about id so it needs to look up different table for concat value of two columns (name ||' - '||site ).
It would be dead simple however there is another curve ball: location_id holds results of shuttle, so it is populated by values like this 34:45:56:67. I need to convert that to:
Orlando - SITE1, Arizona - SITE2, Miami - SITE3
so all those results are returned IN ONE ROW of report
As this is report it can be done by : transffering column report into 'Display as text based on LOV', building PL/SQL block which generates SQL statement and loops through values... etc.
I tried many approaches and I am running out of ideas and time solve this problem. Any help greatly appreciated.
With SQL only (Oracle 11g):
select x.id, x.name, listagg(t2.name || t2.site, ', ') within group (order by t2.id)
from
(
select distinct t1.id, t1.name, regexp_substr(t1.project_id, '[^:]+', 1, level) id_site
from tablel1 t1
connect by level <= regexp_count(t1.project_id, ':') + 1
) x, table22 t2
where t2.id = x.id_site
group by x.id, x.name
This gives:
1 P1 Orlando - SITE1, Arizona - SITE2, Miami - SITE3
2 P2 Orlando - SITE1
3 P3 Arizona - SITE2, Miami - SITE3