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

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

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

Solution

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