Conversion of multiple values within one column

Question

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.

Can you explain more detailed? 34:45:56:67 is ids of table2 (like 34,45,56 and 67) which have column1 and column2? — DARK_A, Jul 09 '13 at 15:12
I find your question a bit unclear. Could you please precise how `column1` and `column2` are linked to `table1`, and why you want 4 values `column1 ||'-'||column2` ? Is this because you have 4 colon-delimited values in `location_id` ? A quick example of what you have in your tables and what you want as output would be great. — Emmanuel, Jul 09 '13 at 15:16
Storing values in colon-delimited string is a very bad solution. You will have many problems with it. — NoGotnu, Jul 10 '13 at 14:37
OK, so from DB design point of view, how would you solve that problem? One way would be to create another table with two columns: ID from table1, and project_id number in the second. However, this will make building forms more painful. Additionally, it also means that you are forced to create thousand of tables making your DB difficult to manage. Can you propose different model? — Jestem_z_Kozanowa, Jul 10 '13 at 14:57
You are wrong. Storing values in three tables will make building your forms more easy. Read about "http://en.wikipedia.org/wiki/Database_normalization". If you still store values like colon-delimited strings, you will always ask questions at this site. — NoGotnu, Jul 11 '13 at 06:59

score 2 · Accepted Answer · answered Jul 09 '13 at 16:24

2

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

answered Jul 09 '13 at 16:24

Emmanuel

13,935
12
50
72

I like this solution more , because it is pure SQL implementation.If I knew it this morning I would save myself two hours. – Jestem_z_Kozanowa Jul 09 '13 at 18:42
The more I look into that answer the more I like it. :P – Jestem_z_Kozanowa Jul 10 '13 at 09:39
This is sure better than mine :) – DARK_A Jul 10 '13 at 14:42

DARK_A · Answer 2 · 2013-07-09T16:02:51.167

Here is procedure:

DECLARE
  CURSOR c (p_id  NUMBER) IS
    SELECT NAME||' - '||SITE
      FROM TABLE2
     WHERE ID = p_id;
  l_tsv     VARCHAR2(1000) := '23:45:56';
  l_item    NUMBER;
  lc_t      VARCHAR(200);
  lc_result VARCHAR2(4000);
BEGIN
  FOR i IN 1 .. LENGTH(l_tsv) - LENGTH(REPLACE(l_tsv, ':', '')) + 1 LOOP
    l_item := REGEXP_SUBSTR(l_tsv, '[^:]+', 1, i);
    OPEN c (l_item);
    FETCH c INTO lc_t;
    CLOSE c;
    lc_result := lc_result ||', '||lc_t;
  END LOOP;
  lc_result := SUBSTR(lc_result,3);
  dbms_output.put_line(lc_result); 
END;

UPDATE

Function:

CREATE OR REPLACE FUNCTION some_name(l_tsv VARCHAR2) RETURN VARCHAR2 IS
  CURSOR c (p_id  NUMBER) IS
    SELECT NAME||' - '||SITE
      FROM TABLE2
     WHERE ID = p_id;
  l_item    NUMBER;
  lc_t      VARCHAR(200);
  lc_result VARCHAR2(4000);
BEGIN
  FOR i IN 1 .. LENGTH(l_tsv) - LENGTH(REPLACE(l_tsv, ':', '')) + 1 LOOP
    l_item := REGEXP_SUBSTR(l_tsv, '[^:]+', 1, i);
    OPEN c (l_item);
    FETCH c INTO lc_t;
    CLOSE c;
    lc_result := lc_result ||', '||lc_t;
  END LOOP;
  lc_result := SUBSTR(lc_result,3);
  RETURN (lc_result);
END some_name;

OK, so that would definitely return correct values, however I am not entirely sure how I would go around adding and displaying those values inside as Apex report column along side other columns in report — Jestem_z_Kozanowa, Jul 09 '13 at 15:57
Use it as function, which returns result string and pass ids as parameter. — DARK_A, Jul 09 '13 at 16:04

score 0 · Answer 3 · answered Jul 11 '13 at 07:14

As you said in comment: make third table with ID from table1 and location_id. Then simply join this tables in your query. But i didn't undestand:

I need to convert that to:

Orlando - SITE1, Arizona - SITE2, Miami - SITE3

so all those results are returned IN ONE ROW of report

is real requirement? What if convert it to

P1 - Orlando - SITE1
P1 - Arizona - SITE2 
P1 - Maimi   - SITE3
P2 - Orlando - SITE1
P3 - Arizona - SITE2 
P3 - Maimi   - SITE3

? If so, you will need one simple join. Or make report without join, and one field make as lookup from another table.

Conversion of multiple values within one column

3 Answers3