sql - 一列内多个值的转换

Question

我在 Oracle Apex 4.2 中工作。我有两个表:

我有简单的报告要构建

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

但是，我遇到的问题是 location_id 仅包含有关 id 的信息，因此它需要查找不同的表以获取两列的 concat 值(名称 ||' - '||site )。

这将非常简单，但是还有另一个曲线球:location_id 保存了穿梭机的结果，因此它由像 34:45:56:67 这样的值填充。我需要将其转换为:

奥兰多 - SITE1，亚利桑那 - SITE2，迈阿密 - SITE3

所以所有这些结果都在报告的一行中返回

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

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

Answer 1

仅使用 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

这给出:

1   P1  Orlando - SITE1, Arizona - SITE2, Miami - SITE3
2   P2  Orlando - SITE1
3   P3  Arizona - SITE2, Miami - SITE3