【Question】

Here’s a table:

StudentID   Course 

1         Chinese,Maths,English

2         Humanities,History,Geography

3         Geography,Chinese

4         Geography,Maths 

 

I need to split the course records into this by StudentID:

1  Chinese

1  Maths

1  English

2  Humanities

2  History

2  Geography

4  Geography

4  Maths

3  Geography

3  Chinese

 

【Answer】

With Oracle, you can use hierarchical queries having subqueries or random numbers to handle this. Here’re two solutions:

Solution 1:

SELECT StudentID,REGEXP_SUBSTR(Course,'\[^,\]+',1,rn) Course 

FROM t0052,

  (SELECT LEVEL rn

   FROM DUAL 

   CONNECT BY LEVEL<=(SELECT

   MAX(length(trim(translate(Course,replace(Course,','),' '))))+1

   FROM t0052)) 

WHERE REGEXP_SUBSTR(Course,'\[^,\]+',1,rn) IS NOT NULL

Solution 2:

select StudentID,regexp_substr(Course,'\[^,\]+',1,level) Course 

from t0052 

connect by level <= (length(Course)-length(regexp_replace(Course,'\[^,\]+',''))) 

and rowid= prior rowid 

and prior dbms_random.value is not null;

 

Oracle hacks are used in both solutions (such as “prior dbms_random.value is not null” for avoiding loop errors). This requires that users should have some Oracle skills). And the program becomes unexecutable in a different database product. Here’s a universal solution written in SPL (Structured Process Language):

	A
1	$SELECT StudentID,Course   FROM  t0052
2	=A1.news(Course.array();StudentID,~:Course)

The script is simple and concise. Below is A2’s result:

StudentID	Course
1	Chinese
1	Maths
1	English
2	Humanities
2	History
2	Geography
3	Geography
3	Chinese
4	Geography
4	Maths

 

esProc offers JDBC interface to be conveniently embedded into a Java application. See How to Call an SPL Script in Java.