Test it:
DECLARE @lessons TABLE(LessonID INT IDENTITY(1,1), LessonName VARCHAR(30))
INSERT INTO @lessons (LessonName)
SELECT 'lesson A'
UNION ALL SELECT 'lesson B'
UNION ALL SELECT 'lesson C'
UNION ALL SELECT 'lesson D'
DECLARE @subject_lesson TABLE (slid INT IDENTITY(1,1), lids VARCHAR(30))
INSERT INTO @subject_lesson (lids)
SELECT '1,2,3'
UNION ALL SELECT '2,3,4'
UNION ALL SELECT '1,3,5'
;WITH Lessons AS
(
SELECT slid, CONVERT(INT, LEFT(lids, CHARINDEX(',', lids)-1)) AS lid, RIGHT(lids, LEN(lids) - CHARINDEX(',', lids)) AS Remainder
FROM @subject_lesson
WHERE CHARINDEX(',', lids)>0
UNION ALL
SELECT slid, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS lid, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM Lessons
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT slid, CONVERT(INT, Remainder) AS Remainder, NULL AS Remainder
FROM Lessons
WHERE CHARINDEX(',', Remainder)=0
)
SELECT L.slid, L.lid, M.LessonName
FROM Lessons AS L LEFT JOIN @lessons AS M ON L.lid = M.LessonID
ORDER BY L.slid, L.lid
Result:
slid id LessonName
1 1 lesson A
1 2 lesson B
1 3 lesson C
2 2 lesson B
2 3 lesson C
2 4 lesson D
3 1 lesson A
3 3 lesson C
3 5 NULL