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.

  • How to design database tables for hierarchical data with unknown depth?
  • SQL server transaction
  • Prevent database file from copying
  • Evaluating GETDATE twice in a statement - will it always evaluate to be the same?
  • Getting only Month and Year from SQL DATE
  • Replacing SQL Data Table with New Data
  • 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.