sql-serversql-server-2008t-sql

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.

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

"INSTITUTION1, INSTITUTION2"

As I didn't supply a where clause I expect my results to show up like this for each row in the database.


Solution

  • Update (As suggested by @Aaron in the comment)

    STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.

    SELECT
        STUDENTNUMBER
        , STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
        , STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
    FROM Education E
    GROUP BY E.STUDENTNUMBER;
    

    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 XmlPathList
    FROM EDUCATION E
    GROUP BY E.STUDENTNUMBER
    

    Here is the FIDDLE showing results for both STRING_AGG and FOR XML PATH('').