Question:
As shown in the figure below, how to use database language implementation will EXCL file in figure into a form, figure 2
Note: 1. The number of subjects' learning subjects' field contains uncertainty;
2. 'learning subjects' subject in the field separator can be a comma or pause.
3. The current in all the subjects' learning subjects' fields after may have Spaces;

CodePudding user response:

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

CodePudding user response:

This method is a little stupid, but can realize

 create table T1 
(
S_name VARCHAR2 (10), 
S_age NUMBER, 
S_class VARCHAR2 (10), 
S_subject VARCHAR2 (100) 
); 

INSERT INTO t1 VALUES (' xiaoming, 10, 'grade 3', 'Chinese, maths, music); 
INSERT INTO t1 VALUES (' the little red, 11, 'fourth graders',' art, mathematics, classical music); 

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 
The FROM t1) 
The SELECT s_name, s_age, s_class, s1 
The FROM a1 
WHERE s1 IS NOT NULL 
UNION ALL 
The SELECT s_name, s_age, s_class, s2 
The FROM a1 
WHERE s2 IS NOT NULL 
UNION ALL 
The SELECT s_name, s_age, s_class, s3 
The FROM a1 
WHERE the 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;