在Chris Shaffer的回答之上：

如果您的数据可能会重复，例如

Tom
Ali
John
Ali
Tom
Mike

而不是汤姆、阿里、约翰、阿里、汤姆、迈克

您可以使用DISTINCT避免重复，并让Tom、Ali、John、Mike：

DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names

SELECT PageContent = Stuff(
    (   SELECT PageContent
        FROM dbo.InfoGuide
        WHERE CategoryId = @CategoryId
          AND SubCategoryId = @SubCategoryId
        for xml path(''), type
    ).value('.[1]','nvarchar(max)'),
    1, 1, '')
FROM dbo.InfoGuide info

我真的很喜欢Dana回答的优雅，只想让它变得完整。

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

在SQL Server vNext中，这将内置STRING_AGG函数。在STRING_AGG（Transact-SQL）中了解有关它的更多信息。

我们可以使用RECUSRSIVITY、WITH CTE、union ALL，如下所示

declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')

declare @myresult as table(id int,str nvarchar(max),ind int, R# int)

;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte

select top 1 str from @myresult order by R# desc

SQL Server 2017+和SQL Azure：STRING_AGG

从SQL Server的下一个版本开始，我们终于可以跨行连接，而无需使用任何变量或XML开关。

STRING_AGG（Transact-SQL）

不分组

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

分组时：

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

带分组和子排序

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;