Comma separated results in SQL

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.

  • SQL Query to Select Prevailing Wind Direction by Date
  • Database '…\DATABASE.MDF' cannot be upgraded because it is read-only or has read-only files
  • how to call web service from t-sql
  • Insert trigger with an IF statement
  • SQL Server: bcp import from a xml file causes error
  • Extract name and prename from email address?
  • 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.

    2 Solutions collect form web for “Comma separated results in SQL”

    Use FOR XML PATH('') and STUFF() as follows Which gives you the same comma seperated result

    SELECT  STUFF((SELECT  ',' + INSTITUTIONNAME
                FROM EDUCATION EE
                WHERE  EE.STUDENTNUMBER=E.STUDENTNUMBER
                ORDER BY sortOrder
            FOR XML PATH('')), 1, 1, '') AS listStr
    
    FROM EDUCATION E
    GROUP BY E.STUDENTNUMBER
    

    Here is the FIDDLE

    DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+', ' ,'') + INSTITUTIONNAME
    FROM EDUCATION
    WHERE STUDENTNUMBER= '111'
    SELECT @listStr
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.