在一列内转换多个值

I am working in Oracle Apex 4.2. I have two tables:

我在Oracle Apex 4.2中工作。我有两张桌子:

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

但是我遇到的问题是location_id只保存有关id的信息,因此需要查找不同的表以获取两列的concat值(名称||' - '|| 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:

它会很简单,但是还有另一个曲线球:location_id保持穿梭的结果,所以它由像34:45:56:67这样的值填充。我需要将其转换为:

Orlando - SITE1, Arizona - SITE2, Miami - SITE3

奥兰多 - SITE1,亚利桑那州 - SITE2,迈阿密 - 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.

由于这是报告,因此可以通过以下方式完成:将列报告转换为“基于LOV显示为文本”,构建生成SQL语句的PL / SQL块并循环通过值...等。

I tried many approaches and I am running out of ideas and time solve this problem. Any help greatly appreciated.

我尝试了很多方法,但我的想法和时间都用完了,解决了这个问题。任何帮助非常感谢。

3 个解决方案

#1

With SQL only (Oracle 11g):

仅限SQL(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

#2

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;

#3

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:

正如您在评论中所述:使用table1和location_id创建具有ID的第三个表。然后只需在查询中加入这些表。但我没有理解:

I need to convert that to:

我需要将其转换为:

Orlando - SITE1, Arizona - SITE2, Miami - SITE3

奥兰多 - SITE1,亚利桑那州 - SITE2,迈阿密 - 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.

?如果是这样,您将需要一个简单的连接。或者在没有连接的情况下生成报告,并且一个字段从另一个表中进行查找

#1