Group by with Comma Separated Values and excluding the value from the previous column value-CodePudding

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    UNIVERSITYADDRESS
102    Mark     Designing   Michigan
103    John     Analytics   Michigan
104    Jacob    Science     Michigan

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 g`roup 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/28f5f/11)

CodePudding user response：

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

CodePudding user response：

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