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 didnt supply a where clause i expect my results to show up like this for each row in the database.

回答1:

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('')), 1, 1, '') AS listStr

FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE

回答2:

For Sql Server 2017 and later you can use the new STRING_AGG function

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

The following example replaces null values with 'N/A' and returns the names separated by commas in a single result cell.

SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv 
FROM Person.Person;

Here is the result set.

John,N/A,Mike,Peter,N/A,N/A,Alice,Bob

Perhaps a more common use case is to group together and then aggregate, just like you would with SUM, COUNT or AVG.

SELECT a.articleId, title, STRING_AGG (tag, ',') AS tags 
FROM dbo.Article AS a       
LEFT JOIN dbo.ArticleTag AS t 
    ON a.ArticleId = t.ArticleId 
GROUP BY a.articleId, title;

回答3:

I just saw another question very similar to this!

Here is the canonical NORTHWIND (spelled just slightly different for some reason) database example.

SELECT *
FROM [NORTHWND].[dbo].[Products]

SELECT CategoryId,
           MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )
      FROM ( SELECT p1.CategoryId, p1.ProductName,
                    ( SELECT COUNT(*) 
                        FROM NORTHWND.dbo.Products p2
                        WHERE p2.CategoryId = p1.CategoryId
                        AND p2.ProductName <= p1.ProductName )
             FROM NORTHWND.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
     GROUP BY CategoryId ;