By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create TABLE Lecturer(
id int NOT NULL,
name VARCHAR(13),
subject VARCHAR(13),
UNIVERSITYADDRESS VARCHAR(60),
Primary Key(id)
);
Create TABLE StudentLecturer(
id int NOT NULL,
Fname VARCHAR(25),
Lname VARCHAR(25),
Lid int
);
Insert INTO Lecturer(id,name,subject,universityaddress)
values(102,'Mark','Designing','Michigan'),
(103,'John','Analytics','Michigan'),
(104,'Jacob','Science','Michigan');
Insert INTO StudentLecturer(id,Fname,Lname,Lid)
values(001,'Jack','P',102),
(001,'Jack','P',103),
(001,'Jack','P',104)
6 rows affected
select *
from StudentLecturer s
outer apply (
select ConcatValues=Stuff((select ', ' + Cast(l.Id as varchar(10))
from Lecturer l where l.id != s.Lid
for xml path('')),1,2,'')
)l
| id | Fname | Lname | Lid | ConcatValues |
|---|---|---|---|---|
| 1 | Jack | P | 102 | 103, 104 |
| 1 | Jack | P | 103 | 102, 104 |
| 1 | Jack | P | 104 | 102, 103 |