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

Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Charlieface, Dec 05 '21 at 10:36

Anton Grig · Accepted Answer · 2021-12-05T21:50:16.650

2

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

edited Dec 05 '21 at 21:50

answered Dec 04 '21 at 23:07

Anton Grig

1,640
7
11

Thank you Grig.It worked.I am trying to see the difference by using STRING_AGG for concatenating but getting an error that STRING_AGG is not recognized built-in function name even tough I set the version to 2019.Attached is the fiddle.https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=52a74006d234107ea5853328a7f9c2af – learner123 Dec 05 '21 at 18:19
1

@learner123 You are welcome. I've published a solution using String_Agg function. It is incorrect to use expressions or subqueries in String_Agg. Note that string_agg is an aggregate function concatenating values in the context of the corresponding group, so in your case you need to specify Group by for the query. You can also specify the order of concatenated results using WITHIN GROUP clause. – Anton Grig Dec 05 '21 at 19:20

score 1 · Answer 2 · answered Dec 04 '21 at 22:54

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

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

2 Answers2