将逗号分隔的值转换为Oracle中的行 [英] Convert comma separated values to rows in Oracle

问题描述

我的数据库中有一个名为student_info的表.其中有多列，其中两列存储用逗号分隔的值

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

我希望输出如下

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

我的查询如下

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?

推荐答案

规范化表要比找到复杂的方法将CSV转换为行要容易得多.

在这种情况下，您可以使用:

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

产生:

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

这篇关于将逗号分隔的值转换为Oracle中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！