Using
CTE[
^]:
CREATE TABLE #table1
(
student varchar(30),
grad varchar(20),
courses varchar(max)
)
INSERT INTO #table1 (student, grad,courses)
VALUES ('Samira','grade1','maths,history,English,science'),
('George','grade2','maths,german,geography')
;WITH CTE AS
(
SELECT 1 AS LoopNo, student, grad, LEFT(courses, CHARINDEX(',', courses)-1) AS course, RIGHT(courses, LEN(courses) -CHARINDEX(',', courses)) AS Remainder
FROM #table1
WHERE CHARINDEX(',', courses)>0
UNION ALL
SELECT LoopNo + 1 AS LoopNo, student, grad, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS course, RIGHT(Remainder, LEN(Remainder) -CHARINDEX(',', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT LoopNo + 1 AS LoopNo, student, grad, Remainder AS course, NULL AS Remainder
FROM CTE
WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM CTE
DROP TABLE #table1
Result:
LoopNo student grad course Remainder
1 Samira grade1 maths history,English,science
1 George grade2 maths german,geography
2 George grade2 german geography
3 George grade2 geography NULL
2 Samira grade1 history English,science
3 Samira grade1 English science
4 Samira grade1 science NULL
To add data into new table, replace
SELECT *
FROM CTE
with
INSERT INTO NewTableName (student, grad, course)
SELECT student, grad, course
FROM CTE
Good luck!