4

I'm new to my job career.. I'm building an SSAS model on Visual Studio Data Tool.. one of the most complex problems I faced is a field contain multiple values that I need to connect everyone of them to another Table eg.

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

I want a Query "not a plsql program" to make it this form

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

to bound it with Persons Table. I found this genius query 

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

it worked totally perfect as I wanted but only on SQL server. When I Tried it on Oracle 11g It gave me error: expected "join" after Cross

can you help me.. I need it as query because I don't have permission to create functions and procedures on the DB by the client policy and I need it as fast as possible

ray
  • 47
  • 7
  • There are several different ways to tokenize strings in Oracle. [This thread shows several approaches](http://stackoverflow.com/q/3710589/146325). – APC Dec 27 '15 at 17:36

2 Answers2

4

SQL Server and Oracle 11g are very different RDBMS and support different syntax(excluding ANSI standard part) so you cannot use CROSS APPLY and XML.

But you can use regexp_substr instead:

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

Output:

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

For delimeters longer than one character you can use slightly modified query presented here.

EDIT:

DISTINCT will remove duplicates so when

PersonsID
1#1#2#5

=> 1 
   2
   5

To get all values with duplicates remove DISTINCT:

=> 1
   1
   2
   5
Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you it worked perfectly great.. but I didn't understand why the "distinct" in the query? – ray Dec 28 '15 at 04:59
  • @ray Distinct is for removing duplicates from `1#1#2#5` like http://sqlfiddle.com/#!4/101b5/1/0. If you want duplicates values remove `DISTINCT`. – Lukasz Szozda Dec 28 '15 at 08:11
1

Below is another way to achieve same result

SELECT family , trim(COLUMN_VALUE) PersonsID
FROM table1, xmltable(('"' || REPLACE(PersonsID, '#', '","') || '"'))
Vidya Pandey
  • 211
  • 2
  • 10