如何编写Oracle查询以将字符串按类别拆分为多行

Question

我是一个新手.. 我正在使用Visual Studio Data Tool建立一个SSAS模型.. 我面临的最复杂的问题之一是一个字段包含多个值，我需要将它们中的每一个连接到另一个表格，例如。

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

我需要一个查询语句（不是PL/SQL程序）来使其成为这种形式。

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

把它与Persons表绑定。 我发现这个天才的查询。

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 Server上，我的代码完美运行，但是在尝试在Oracle 11g上运行时，它报错了："Cross"后面应该跟一个"join"。

你能帮我吗？因为客户端政策的限制，我没有创建函数和过程的权限，所以我需要快速得到查询结果。

- ray

---

在Oracle中，有几种不同的方法可以对字符串进行标记化。这个线程展示了几种方法。 - APC

Answer 1

SQL Server和Oracle 11g是非常不同的关系型数据库管理系统，支持不同的语法（除了ANSI标准部分），所以您不能使用CROSS APPLY和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) 

SqlFiddle演示

输出：

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

对于多个字符的分隔符，您可以使用略微修改后的查询，详见此处。 编辑: DISTINCT将删除重复项，因此当...

PersonsID
1#1#2#5

=> 1 
   2
   5

为获取所有重复值，请移除DISTINCT关键字：

=> 1
   1
   2
   5

- Lukasz Szozda

---

谢谢，它完美地运行了。但是我不明白查询中的“distinct”是为什么？ - ray

@ray 的作用是从 1#1#2#5 中去除重复项，类似于 http://sqlfiddle.com/#!4/101b5/1/0。如果您想要去除重复值，请删除 DISTINCT。 - Lukasz Szozda

Answer 2

以下是另一种实现相同结果的方法。

SELECT family , trim(COLUMN_VALUE) PersonsID
FROM table1, xmltable(('"' || REPLACE(PersonsID, '#', '","') || '"'))

- Vidya Pandey