我有這個程式碼：

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

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

SELECT [Month], Salary FROM @MyTable;

輸出：

我想連接薪水（按月分組），這樣它就會NVARCHAR像這樣：‘2000,3100’

我將如何有效地做到這一點？

SQL Server 2017 之前的版本

嘗試這樣的事情..

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;

如果您使用的是 SQL Server 2017+，則有一個內置函式，它比帶有 XML 和 STUFF 等的子查詢更易於使用。

您可以使用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 

引用自：https://dba.stackexchange.com/questions/96039