Convert comma separated values to rows in Oracle

Question

I have a table named student_info in my database.There are multiple columns out of which two columns store comma separated values

class_id     student                marks  

----------------------------------------------
1             tom,jam,tim            55,65,75
2             rim,gum,ram            33,66,77

i want the output to be as follows

class_id      student       marks

------------------------------------------------
1              tom            55
1              tom            65
1              tom            75
1              jam            55
1              jam            65
1              jam            75
1              tim            55
1              tim            65
1              tim            75

my Query as below

SELECT student_id,TRIM(REGEXP_SUBSTR(student, '[^,]+', 1, level)) student_name
FROM STUDENT_INFO

CONNECT BY level <= REGEXP_COUNT(student, '[^,]+')
    AND PRIOR student = student AND marks = marks
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

with the above query i can get the output as below

class_id  student_name  marks
------------------------------
1           tom         55,65,75
1           jam         55,65,75
1           tim         55,65,75

How to achieve the desired output ?any suggestions?

Answer 1

It would be lot easier to normalize table than finding complicated methods to convert csv to rows.

In this case, you can use this:

with t(class_id, student, marks) as (
    select 1, 'tom,jam,tim', '55,65,75' from dual union all
    select 2, 'rim,gum,ram', '33,66,77' from dual
)
select
    t.class_id,
    regexp_substr(student,'[^,]+',1,x.column_value) student,
    regexp_substr(marks,'[^,]+',1,y.column_value) marks
from t, table(
    cast(
        multiset(
            select level
            from dual
            connect by level <= regexp_count(t.student,',') + 1
        ) as sys.odcinumberlist
    )
)x,table(
    cast(
        multiset(
            select level
            from dual
            connect by level <= regexp_count(t.marks,',') + 1
        ) as sys.odcinumberlist
    )
)y

Produces:

+----------+------------+-------+
| CLASS_ID | STUDENT    | MARKS |
+----------+------------+-------+
| 1        | tom        | 55    |
+----------+------------+-------+
| 1        | tom        | 65    |
+----------+------------+-------+
| 1        | tom        | 75    |
+----------+------------+-------+
| 1        | jam        | 55    |
+----------+------------+-------+
| 1        | jam        | 65    |
+----------+------------+-------+
| 1        | jam        | 75    |
+----------+------------+-------+
| 1        | tim        | 55    |
+----------+------------+-------+
| 1        | tim        | 65    |
+----------+------------+-------+
| 1        | tim        | 75    |
+----------+------------+-------+
| 2        | rim        | 33    |
+----------+------------+-------+
| 2        | rim        | 66    |
+----------+------------+-------+
| 2        | rim        | 77    |
+----------+------------+-------+
| 2        | gum        | 33    |
+----------+------------+-------+
| 2        | gum        | 66    |
+----------+------------+-------+
| 2        | gum        | 77    |
+----------+------------+-------+
| 2        | ram        | 33    |
+----------+------------+-------+
| 2        | ram        | 66    |
+----------+------------+-------+
| 2        | ram        | 77    |
+----------+------------+-------+

Answer 2

Hierarchical Query:

Use correlated-hierarchical queries to find the sub-strings once and then apply the cross product to join them:

SELECT class_id,
       s.COLUMN_VALUE AS student,
       m.COLUMN_VALUE AS mark
FROM   table_name t,
       TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.students, '[^,]+', 1, LEVEL )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.students, '[^,]+' )
           ) AS SYS.ODCIVARCHAR2LIST
         )
       ) s,
       TABLE(
         CAST(
           MULTISET(
             SELECT TO_NUMBER( REGEXP_SUBSTR( t.marks, '[^,]+', 1, LEVEL ) )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.marks, '[^,]+' )
           ) AS SYS.ODCIVARCHAR2LIST
         )
       ) m

Output:

CLASS_ID STUDENT MARKS
-------- ------- -----
       1 tom        55
       1 tom        65
       1 tom        75
       1 jam        55
       1 jam        65
       1 jam        75
       1 tim        55
       1 tim        65
       1 tim        75
       2 rim        33
       2 rim        66
       2 rim        77
       2 gum        33
       2 gum        66
       2 gum        77
       2 ram        33
       2 ram        66
       2 ram        77

Recursive Subquery Factoring:

WITH cte ( class_id, student, marks, s, m, smax, mmax ) AS (
  SELECT class_id,
         student,
         marks,
         1,
         1,
         REGEXP_COUNT( student, '[^,]+' ),
         REGEXP_COUNT( marks, '[^,]+' )
   FROM  table_name
UNION ALL
  SELECT class_id,
         student,
         marks,
         CASE WHEN m = mmax THEN s + 1 ELSE s END,
         CASE WHEN m = mmax THEN 1 ELSE m + 1 END,
         smax,
         mmax
   FROM  cte
   WHERE m < mmax OR s < smax
)
SELECT class_id,
       REGEXP_SUBSTR( student, '[^,]+', 1, s ) AS student,
       TO_NUMBER( REGEXP_SUBSTR( mark, '[^,]+', 1, m ) ) AS mark
FROM   cte;

Answer 3

Using ora:tokenizer

  with t(class_id, student, marks) as (
        select 1, 'tom,jam,tim', '55,65,75' from dual union all
        select 2, 'rim,gum,ram', '33,66,77' from dual
    )
    select class_id,new_student,new_marks from t
     ,xmltable('for $i in ora:tokenize($students,",") return $i' passing student as "students" columns new_student varchar2(20) path '.')
     ,xmltable('for $j in ora:tokenize($marks,",") return $j' passing marks as "marks" columns new_marks varchar2(20) path '.')

Answer 4

try this

 with temp as
(
    select 1 class_id     , 'tom,jam,tim' student, '55,65,75' marks    from dual
    union all
    select 2 class_id     , 'rim,gum,ram' student, '33,66,77' marks    from dual
)
select distinct
  t.class_id,
  trim(regexp_substr(t.student, '[^,]+', 1, students.column_value))  as students,
  trim(regexp_substr(t.marks, '[^,]+', 1, marks.column_value))  as marks
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.student, '[^,]+'))  + 1) as sys.OdciNumberList)) students,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.marks, '[^,]+'))  + 1) as sys.OdciNumberList)) marks
order by class_id;