By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Teachers
(
TeacherID INT PRIMARY KEY NOT NULL,
TeacherName VARCHAR(45) NOT NULL
);
CREATE TABLE Classes
(
ClassID INT PRIMARY KEY NOT NULL,
Class VARCHAR(45) NOT NULL,
MainTeacher INT NOT NULL,
CONSTRAINT FK_Classes_MainTeacher FOREIGN KEY (MainTeacher) REFERENCES Teachers (TeacherID)
);
INSERT INTO Teachers (TeacherID, TeacherName) VALUES
(1, 'Teacher1'),
(2, 'Teacher2');
INSERT INTO Classes (ClassID, Class, MainTeacher) VALUES
(1, 'Class1', 1),
(2, 'Class2', 1),
(3, 'Class3', 2);
5 rows affected
SELECT T.*,
STUFF(
(
SELECT DISTINCT ',' + Class
FROM Classes C
WHERE C.MainTeacher = T.TeacherID
FOR XML PATH('')
), 1, 1, ''
) ControlledClasses
FROM Teachers T
| TeacherID | TeacherName | ControlledClasses |
|---|---|---|
| 1 | Teacher1 | Class1,Class2 |
| 2 | Teacher2 | Class3 |