The inside of the raw data as follows, you need to result column subjects read a record alone
Class students student id
Five threes' 00001 Chinese, 100 | maths, 95 | English, 100
Six li si '00002 Chinese, 92 | maths, 99 | English, 86
A detective '00003 Chinese, 89 | maths, 100 | English, 91

Implementation effect is as follows:
Class student id discipline score
Five threes' 00001 Chinese 100
Five threes 00001 math 95
'Five threes' 00001 English 100
Six li si '00002 Chinese 92
Six li si 00002 math 99
'Six li si '00002 English 86
A detective 00003 Chinese 89
'A detective 00003 math 100
'A detective 00003 English 91
'
I ask, how through SQL implementation, thank you,

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([class] nvarchar (21), [students] nvarchar (22), [student id] nvarchar (25), [result] nvarchar (39)) 
Insert # T 
Select N '5', N 'Joe', N '00001', N '100 | Chinese, mathematics, 95 | English, 100' union all 
Select N '6', N 'bill', N '00002', N '92 | Chinese, mathematics, 99 | English, 86' union all 
Select N 'a', N 'Cathy', N '00003', N '89 | Chinese, mathematics, 100 | English, 91' 
Go 
- the end of the test data 
SELECT the class and grade, 
Student, 
Student id, 
The SUBSTRING (t.v alue, 1, CHARINDEX (', ', t.v alue) - 1) AS subjects, 
The SUBSTRING (t.v alue, CHARINDEX (', ', t.v alue) + 1, LEN (t.v alue)) 
 the AS resultsThe FROM # T 
CROSS the APPLY 
(SELECT * FROM STRING_SPLIT (grades, '|')) t;