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