Thom A - Tuesday, December 12, 2017 5:59 AM

This is a little bit of a "quick answer", as I'm sure there's faster, however, you could use the DelimitedSplit8K function:
SELECT t.StudentID,
       t.StudentName,
       C1.Item AS Code1,
       C2.Item As Code2,
       C3.Item AS Code3
FROM #tStudents t
    CROSS APPLY dbo.DelimitedSplit8K(t.Code1, ',') C1
    CROSS APPLY dbo.DelimitedSplit8K(t.Code2, ',') C2
    CROSS APPLY dbo.DelimitedSplit8K(t.Code3, ',') C3
WHERE C1.ItemNumber = C2.ItemNumber AND C2.ItemNumber = C3.ItemNumber;