What is the most efficient way to concatenate strings in SQL Server?

Question

I have this code:

DECLARE @MyTable AS TABLE
(
   [Month] INT,
   Salary INT
);

INSERT INTO @MyTable VALUES (1,2000), (1,3100);

SELECT [Month], Salary FROM @MyTable;

Output:

I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'

How would I do this efficiently?

Answer 1

SQL Server pre-2017

try something like this..

DECLARE @MyTable AS TABLE
(
   [Month] INT,
   Salary INT
);

INSERT INTO @MyTable VALUES (1,2000), (1,3100);


SELECT [Month],
    STUFF((
    SELECT ',' + CONVERT(NVARCHAR(30),Salary)
    from @MyTable
    WHERE Month = out.Month
    FOR XML Path(''))
    ,1,1,'') csv
 FROM @MyTable out
 GROUP BY Month;

Answer 2

If you're on SQL Server 2017+, there's a built-in function that's a bit simpler to use than a subquery with XML and STUFF, etc.

You can use STRING_AGG.

SELECT 
    p.OwnerUserId, 
    aggro = 
        STRING_AGG
        (
            p.Score, 
            ', '
        )
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId


SELECT 
    p.OwnerUserId, 
    aggro_order = 
        STRING_AGG
        (
            p.Score, 
            ', '
        )                        
        WITHIN GROUP 
        (
            ORDER BY 
                p.Score DESC
         )
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId