7

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:

Table Graphic

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?

5

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;
8

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, STRING_AGG(p.Score, ', ') AS aggro
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.