Group by with comma-separated values and excluding the value from the previous column value

Question

I have two tables Lecturer and Student and I would like to display the other lecturer id's in the new column with comma separated values. I am using SQL Server 2014.

Table: Lecturer

id     name     subject   
-------------------------------
102    Mark     Designing  
103    John     Analytics   
104    Jacob    Science     

Table: StudentLecturer

id     Fname    Lname       Lid
--------------------------------
1      Jack     P           102
1      Jack     P           103
1      Jack     P           104

By using group by I am getting a single value as below:

SELECT  id, fname, lname, lid 
FROM studentlecturer 
GROUP BY id

Table: StudentLecturer

    id     Fname    Lname       Lid
    --------------------------------
    1      Jack     P           102

Expected result

id     Fname    Lname       Lid      Concat Values
---------------------------------------------------
1      Jack     P           102      103,104
1      Jack     P           103      102,104
1      Jack     P           104      102,103

SQL Fiddle: http://sqlfiddle.com/#!7/73304

Answer 1

This can be done using For XML Path("), TYPE as follows:

SELECT S.id, S.Fname, S.Lname, S.Lid, 
       STUFF((SELECT Concat(',',Lid) FROM StudentLecturer WHERE id=S.id And Lid<>S.Lid
       FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS [Concat Values]  
FROM StudentLecturer As S 

with using String_Agg function for SQL Server 2017 (14.x) and later versions.

SELECT S.id, S.Fname, S.Lname, S.Lid, 
       STRING_AGG(S_1.Lid, ',') WITHIN GROUP (ORDER BY S_1.Lid) AS [Concat Values]  
FROM StudentLecturer AS S LEFT JOIN StudentLecturer AS S_1 ON (S.id=S_1.id AND
                                                               S.Lid<>S_1.Lid)
GROUP BY S.id, S.Fname, S.Lname, S.Lid
ORDER BY S.Fname, S.Lname, S.Lid

Answer 2

You can do this utilising for xml to aggregate your values. Stuff removes the initial comma.

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,1,'')
)l

Working Fiddle