Comma separated results in SQL

Question

I have the following code which will create a comma delimited list for my results:

DECLARE @listStr VARCHAR(MAX) SELECT @listStr = COALESCE(@listStr+', ' ,'') + INSTITUTIONNAME FROM EDUCATION WHERE STUDENTNUMBER= '111' SELECT @listStr 

The problem is its creating one massive comma delimited line. I need it to return a comma separated list per row.

So if Simon has been part of 2 institutions, then i expect:

"INSTITUTION1, INSTITUTION2" 

As i didnt supply a where clause i expect my results to show up like this for each row in the database.

Answer 1

Update (As suggested by @Aaron in the comment)

STRING_AGG is the preferred way of doing this in the modern versions of SQL Server.

Original Answer:

Use FOR XML PATH('') - which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result

SELECT  STUFF((SELECT  ',' + INSTITUTIONNAME             FROM EDUCATION EE             WHERE  EE.STUDENTNUMBER=E.STUDENTNUMBER             ORDER BY sortOrder             FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')            , 1, LEN(','), '') AS listStr  FROM EDUCATION E GROUP BY E.STUDENTNUMBER 

Here is the FIDDLE