Table: students
==============================
| TeacherID  | SName         |
| 1          | Thompson      |
| 1          | Nickles       |
| 2          | Cree          |
==============================

Table: teacher
====================================================
| TeacherID  | TName         | + many other fields |
| 1          | Pipers        |                     |
| 2          | Slinger       |                     |
====================================================

================================================================
| TeacherName | many other fields | Students                   |
| Pipers      |                   | Thompson,Nickles           |
================================================================

SELECT *
FROM teacher
LEFT JOIN (
    SELECT DISTINCT
        EL2.teacherID,
        STUFF(( SELECT ',' + SName
        FROM students
        WHERE EL2.teacherID = students.teacherID
        FOR XML PATH('')
        ),1,1,'') AS "Students"
    FROM students, teacher EL2) t1
    ON t1.teacherID = teacher.teacherID
WHERE t1.Students LIKE '%Thompson%'