数据库语言处理EXCL中的表信息

[zhu39860]

问题：
如下图所示，如何用数据库语言实现将 EXCL文件中的图一表格转换成图二。
注意点：1.‘所学科目’字段中包含的科目个数不确定；
2.‘所学科目’字段中各科目的分隔符有可能是逗号或者顿号；
3.‘所学科目’字段中各科目前后有可能有空格；

[qq_30421439]

select distinct name,age,grade,regexp_substr(subject,'[^[:punct:]]+',1,level) subject 
from test
connect by level<=regexp_count(subject,'[[:punct:]]')+1;

[zcs_zzh]

这个方法有点笨，但是能实现

create table T1
(
  s_name    VARCHAR2(10),
  s_age     NUMBER,
  s_class   VARCHAR2(10),
  s_subject VARCHAR2(100)
);

INSERT INTO t1 VALUES ('小明', 10, '三年级', '语文、数学、音乐');
INSERT INTO t1 VALUES ('小红', 11, '四年级', '美术、数学、古典音乐');

WITH a1 AS
 (SELECT s_name, s_age, s_class, regexp_substr(s_subject, '[^、]+', 1, 1) s1,
         regexp_substr(s_subject, '[^、]+', 1, 2) s2, regexp_substr(s_subject, '[^、]+', 1, 3) s3,
         regexp_substr(s_subject, '[^¡¢]+', 1, 4) s4
    FROM t1)
SELECT s_name, s_age, s_class, s1
  FROM a1
 WHERE s1 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s2
  FROM a1
 WHERE s2 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s3
  FROM a1
 WHERE s3 IS NOT NULL
UNION ALL
SELECT s_name, s_age, s_class, s4 FROM a1 WHERE s4 IS NOT NULL ORDER BY s_name;