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?

It seems pretty easy to apply the same technique again, to the output you were able to get already. Just apply it to the `marks` column. Are you sure, though, that the requirement is correct? Getting all possible combinations of `(student,mark)` seems exceptionally odd; are you sure you shouldn't pair the first student with the first mark, the second student with the second mark, etc.? Also - why do you need `and marks = marks` in your current query? What does it do? — , Jan 17 '17 at 14:27
This is a bad database design, so your best bet would be to change this rather than to write queries that have to muddle through it. — Thorsten Kettner, Jan 17 '17 at 14:54

Gurwinder Singh · Answer 1 · 2017-01-17T15:40:48.503

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    |
+----------+------------+-------+

MT0 · Answer 2 · 2017-01-17T15:08:50.497

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;

score 0 · Answer 3 · answered Jan 17 '17 at 16:32

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 '.')

score 0 · Answer 4 · answered Jan 18 '17 at 06:01

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;

Convert comma separated values to rows in Oracle

4 Answers4

Linked