oracle - 如何编写 oracle Query 将字符串拆分为按类别相关的多行

Question

我是新的职业生涯..
我正在 Visual Studio 数据工具上构建 SSAS 模型。
我面临的最复杂的问题之一是一个字段包含多个值，我需要将它们中的每个人连接到另一个表，例如。

Family   PersonsID 
1         1#2#5 
2         6#7#10

我想要一个查询“不是 plsql 程序”来使它成为这种形式

Family  PersonsID
1          1
1          2
1          5
2          6
2          7
2          10

将其与 Persons 表绑定(bind)。
我发现了这个天才查询

SELECT A.[Family],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([PersonsID], '#', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

它完全符合我的要求，但仅在 SQL 服务器上运行。当我在 Oracle 11g 上尝试时
它给了我错误:预期在 Cross 之后“加入”

你能帮我吗..我需要它作为查询，因为我无权通过客户端策略在数据库上创建函数和过程，我需要它尽快

Answer 1

SQL Server和 Oracle 11g是非常不同的 RDBMS 并且支持不同的语法(不包括 ANSI 标准部分)，所以你不能使用 CROSS APPLY and XML .

但是你可以使用 regexp_substr反而:

SELECT DISTINCT t.Family,
  TRIM(regexp_substr(t.PersonsID, '[^#]+', 1, levels.column_value)) AS PersonsID
FROM TableA t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.PersonsID, '[^#]+'))  + 1) as sys.OdciNumberList)) levels
ORDER BY Family, CAST(PersonsID AS INT) 

SqlFiddleDemo

输出:

╔═════════╦═══════════╗
║ FAMILY  ║ PERSONSID ║
╠═════════╬═══════════╣
║      1  ║         1 ║
║      1  ║         2 ║
║      1  ║         5 ║
║      2  ║         6 ║
║      2  ║         7 ║
║      2  ║        10 ║
╚═════════╩═══════════╝   

对于超过一个字符的分隔符，您可以使用稍微修改的查询 here .

编辑:
DISTINCT将删除重复项，所以当

PersonsID
1#1#2#5

=> 1 
   2
   5

要获取所有重复值，请删除 DISTINCT :

=> 1
   1
   2
   5